Forum Discussion

third_hicana's avatar
third_hicana
Icon for Helper IV rankHelper IV
11 months ago
Solved

Transpose/ Transform Rows to Columns

Hi. Would like to ask your help on my case. I am trying to transform Table1 tpo Table2. I am struggle in transposing first two rows into columns. I would gteatly appreacite your help.  ...
Need Help
Show and Tell
Tips and Tricks
  • jgeddes's avatar
    jgeddes
    11 months ago

    Here is one way you can do this...

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMlTQVQjPL8ouyElMTlUITi0pLQCKApERUMIppzS1oCgzrwQkFKsTreTpAmSBFSkElyQWlcB5zvm5BTmpJalAAbgmuJKg1LLM1HKEGpBJhiC79Y30jQyMTIFMY31jGNNI3wTGBKs0AkubwsRADBjbTN8QLm4BMyw2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Transposed Table" = Table.Transpose(Source),
    #"Replaced Value" = Table.ReplaceValue(#"Transposed Table","",null,Replacer.ReplaceValue,{"Column1"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"|ID"}, "Attribute", "Date"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Category", "Sub-Category"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Category", type text}, {"Sub-Category", type text}, {"|ID", Int64.Type}, {"Date", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"|ID", "ID"}})
in
    #"Renamed Columns"
m_dekorte's avatar
m_dekorte
Icon for Resident Rockstar rankResident Rockstar
11 months ago

Here's a classic UI approach, you can explore, made more dynamic.

let
    Source = YourTable,
    n = List.PositionOf(Table.Column(Source, List.First(Table.ColumnNames(Source))), "ID", Occurrence.First),
    headerRows = Table.FirstN(Source, n+1),
    replBlank = Table.ReplaceValue(headerRows,"",null,Replacer.ReplaceValue,Table.ColumnNames(headerRows)),
    Transpose1 = Table.Transpose(replBlank),
    fillDown = Table.FillDown(Transpose1,{List.First(Table.ColumnNames(Transpose1))}),
    mergeCols = Table.TransformColumns( Table.CombineColumns(fillDown,Table.ColumnNames(Transpose1),Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Header"), {}, each Text.TrimStart(_, "|")),
    Transpose2 = Table.Transpose(mergeCols) & Table.Skip(Source, n+1),
    promoteHeader = Table.PromoteHeaders(Transpose2, [PromoteAllScalars=true]),
    unpivotOthers = Table.UnpivotOtherColumns(promoteHeader, {"ID"}, "Category", "Date"),
    splitCol = Table.SplitColumn(unpivotOthers, "Category", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Category", "Sub-Category"})
in
    splitCol