Forum Discussion
Unpivoting nested column headers
Hi,
I need to transform the below data structure (I've uploaded to https://ufile.io/gv802ie0):
into the below data structure:
I'm used to tranformations but don't understand how to deal with the nested column headers (Branch being on top of month). Please can you tell me how to transform the data using the tranformation buttons/tools (is it possible to not to have to edit the M code directly? (we have no experience in M code))
Many thanks for any help,
CM
Anonymous
Please try this
See the attached file as well
You can follow the steps from the Query Editor
All of this was user Interface driven (No manual coding required)
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}}), #"Transposed Table" = Table.Transpose(#"Changed Type"), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table", {{"Column1", type text}, {"Column2", type text}}, "en-US"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"), #"Transposed Table1" = Table.Transpose(#"Merged Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{",", type text}, {"Branch,Month", type text}, {"1000,10/1/2018 12:00:00 AM", type number}, {"1000,11/1/2018 12:00:00 AM", type number}, {"1000,12/1/2018 12:00:00 AM", type number}, {"1001,10/1/2018 12:00:00 AM", type number}, {"1001,11/1/2018 12:00:00 AM", type number}, {"1001,12/1/2018 12:00:00 AM", type number}, {"1002,10/1/2018 12:00:00 AM", type number}, {"1002,11/1/2018 12:00:00 AM", type number}, {"1002,12/1/2018 12:00:00 AM", type number}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {",", "Branch,Month"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", Int64.Type}, {"Attribute.2", type datetime}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{",", "Account"}, {"Branch,Month", "Sub Account"}, {"Attribute.1", "Branch"}, {"Attribute.2", "Month"}}) in #"Renamed Columns"
2 Replies
Anonymous
Please try this
See the attached file as well
You can follow the steps from the Query Editor
All of this was user Interface driven (No manual coding required)
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}}), #"Transposed Table" = Table.Transpose(#"Changed Type"), #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table", {{"Column1", type text}, {"Column2", type text}}, "en-US"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"), #"Transposed Table1" = Table.Transpose(#"Merged Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{",", type text}, {"Branch,Month", type text}, {"1000,10/1/2018 12:00:00 AM", type number}, {"1000,11/1/2018 12:00:00 AM", type number}, {"1000,12/1/2018 12:00:00 AM", type number}, {"1001,10/1/2018 12:00:00 AM", type number}, {"1001,11/1/2018 12:00:00 AM", type number}, {"1001,12/1/2018 12:00:00 AM", type number}, {"1002,10/1/2018 12:00:00 AM", type number}, {"1002,11/1/2018 12:00:00 AM", type number}, {"1002,12/1/2018 12:00:00 AM", type number}}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {",", "Branch,Month"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.1", Int64.Type}, {"Attribute.2", type datetime}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{",", "Account"}, {"Branch,Month", "Sub Account"}, {"Attribute.1", "Branch"}, {"Attribute.2", "Month"}}) in #"Renamed Columns"Brilliant, thanks Zubair_Muhammad !