Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Promote grouped rows to column headers

Hi,    I have this kind of table : Group 1     Group 2   Group 3   Group … Column 1 Column 2 Column 3 Column 1 Column 2 Column 1 Column … Column … … … … … … … … … ...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    M code for your reference. 

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKc3NUzBU0oExjRBMYwQTuwIU0dhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Group 1" = _t, #"(blank)" = _t, #"(blank).1" = _t, #"Group 2" = _t, #"(blank).2" = _t, #"Group 3" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group 1", type text}, {"(blank)", type text}, {"(blank).1", type text}, {"Group 2", type text}, {"(blank).2", type text}, {"Group 3", type text}, {"(blank).3", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Added Conditional Column" = Table.AddColumn(#"Transposed Table", "Custom", each if Text.Contains([Column1], "blank") then null else [Column1]),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Custom"}),
    #"Added Custom" = Table.AddColumn(#"Filled Down", "Custom.1", each [Column2]&[Custom]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Column1", "Column2", "Custom"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column 1Group 1", type any}, {"Column 2Group 1", type any}, {"Column 3Group 1", type any}, {"Column 1Group 2", type any}, {"Column 2Group 2", type any}, {"Column 1Group 3", type any}, {"Column 2Group 3", type any}})
in
    #"Changed Type2"

     

     

Nolock's avatar
Nolock
Resident Rockstar
7 years ago

Hi Anonymous,

I've tried to find a solution for you. Here we go. It takes first 2 rows, transforms them separately and then combine them with the source again. The solution contains also some sample data.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci/KLy1QMFTSUYIgCN8ImWMM4sTqRCs55+eU5uaBFUOZRgimMYKJXQGKKMi4tPx8oFBSYhGQrKisApKJSclwhyjFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    
    // take first 2 rows and transform them
    Source2FirstRows = Table.FirstN(Source, 2),
    #"Transposed Table" = Table.Transpose(Source2FirstRows),
    #"Replaced Value" = Table.ReplaceValue(#"Transposed Table","",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filled Down", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column2", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Column2.1", "Column2.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Column2.2"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each [Column1.2] & "." & [Column2.1]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Column1.2", "Column2.1"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Columns1"),
    
    // remove first 2 rows from Source
    SourceWithout2FirstRows = Table.RemoveFirstN(Source, 2),
    // combine new transformed 2 rows and Source without first 2 rows
    CombineTables = Table.Combine({#"Transposed Table1", SourceWithout2FirstRows})
in
    CombineTables

And screenshots:

BeforeAfter