Hello,
thanks to everyone who participated! This time renaming with List.Zip was the clear favorite and for the transformation most used Date.ToText, Date.AddMonths and DateTime.LocalNow().
I’ll show you first an approach with several UI-supported steps, but I admit it right away, it does include Date.AddMonths and DateTime.LocalNow() in a custom column. These are not beginner level functions, but as they are very important, it makes sense to learn them early on.
The second solution uses the mainstream List.Zip approach but with a variant in the month definition and hopefully in an easily understandable way.
Let’s start off with marking the Customer column (resp. all non-month columns) and then on the Transform tab select Unpivot Columns > Unpivot Other Columns:
Right-click on the Attribute column and select Replace Values:
Type in Month as Value to Find and leave Replace With empty:
Right-click on the Attribute column and select Change Type > Whole Number:
On the Added Column tab select Custom Column:
You can name the column Date and we use the Month column for the date definition. DateTime.LocalNow() gives you the current DateTime, we add the number from Month minus 1 and turn the result into a date:
= Date.From(Date.AddMonths(DateTime.LocalNow(), [Attribute]-1))
It would make a lot of sense to wrap this in Date.ToText and format the dates as required:
= Date.ToText(Date.From(Date.AddMonths(DateTime.LocalNow(), [Attribute]-1)), “MMM-yy”, “en-US”)
But let’s assume you don’t know that function and it’s too complex for you. So we go on a UI-supported detour.
Mark the Date column and on the Added Column tab select Date > Month > Name of Month:
Mark the created Month Name column and on the Transform tab select Extract > First Characters:
Type in 3:
For the year mark the Date column and repeat on the Transform tab with Extract > Last Characters and this time type in 2:
Mark first the Month Name column and then the Date column, before right-clicking and selecting Merge Columns:
As separator go for Custom and type in a dash. You can keep Merged as column name:
Now we are at the end of the detour, the following 2 steps are also necessary if you use Date.ToText above.
Mark the Attribute column and press delete to get rid of it.
Finally mark the Merged column and on the Transform tab select Pivot Column and as Value Column choose Value:
Done! 10 steps if you don’t wrap in Date.ToText(…, “MMM-yy”, “en-US”). 4 less if you do use it.
let
Source = Excel.CurrentWorkbook(){[Name="FC"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns","Month","",Replacer.ReplaceText,{"Attribute"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Attribute", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each Date.From(Date.AddMonths(DateTime.LocalNow(), [Attribute]-1)) ),
#"Inserted Month Name" = Table.AddColumn(#"Added Custom", "Month Name", each Date.MonthName([Date], "en-US"), type text),
#"Extracted First Characters" = Table.TransformColumns(#"Inserted Month Name", {{"Month Name", each Text.Start(_, 3), type text}}),
#"Extracted Last Characters" = Table.TransformColumns(#"Extracted First Characters", {{"Date", each Text.End(Text.From(_, "de-DE"), 2), type text}}),
#"Merged Columns" = Table.CombineColumns(#"Extracted Last Characters",{"Month Name", "Date"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
#"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Merged]), "Merged", "Value", List.Sum)
in
#"Pivoted Column"
Quite some effort for the name change. Enough reason to learn and apply a renaming technique with List.Zip. The syntax is quite simple and we “just” need a list of the old column names and a list of the new column names:
= Table.RenameColumns(prevStep, List.Zip( { OldColumnNames, NewColumnNames } ) )
The relevant old column names are all columns which start with Month:
= List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, “Month”))
For building the new column names dynamically you can use Table.ColumnCount, List.Count or List.PositionOf. I’ll transform here the MonthColumns with the same approach as above:
= List.Transform(
MonthColumns,
each Date.ToText(
Date.AddMonths(Date.From(DateTime.LocalNow()), Number.From(Text.AfterDelimiter(_, “Month”))),
“MMM-yy”,
“en-US”
)
)
Now that we have the list of the old column names and the list of the new column names we can reference to the Source step to rename the columns:
= Table.RenameColumns(Source, List.Zip({MonthColumns, NewNames}))
let
Source = Excel.CurrentWorkbook(){[Name = "FC"]}[Content],
MonthColumns = List.Select(Table.ColumnNames(Source), each Text.StartsWith(_, "Month")),
NewNames = List.Transform(
MonthColumns,
each Date.ToText(
Date.AddMonths(Date.From(DateTime.LocalNow()), Number.From(Text.AfterDelimiter(_, "Month"))),
"MMM-yy",
"en-US"
)
),
#"Renamed Columns" = Table.RenameColumns(Source, List.Zip({MonthColumns, NewNames}))
in
#"Renamed Columns"
That follows a simple three step structure but as you can see in some of the solutions it can even be written more compact.
Try both approaches and make sure to check also some of the other solutions above! => Hope you learn something!
Big thanks to everyone who has blurred or hidden the details! I have taken this now out so that it’s easier to compare solutions.