Unpivoting nested column headers

[Anonymous]

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

[Zubair_Muhammad · Accepted solution]

@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"

[Anonymous]

Brilliant, thanks @Zubair_Muhammad !