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"
GeraldGEmerick's avatar
GeraldGEmerick
Icon for Memorable Member rankMemorable Member
11 months ago

third_hicana Here is one possible method:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRMlTQVQjPL8ouLkhMTlUITi0pLQCKApERUMIppzS1oCgzrwQkFKsTreTpAmSBFSkElyQWlcB5zvm5BTmpJalAAbgmuBKECFwZyDBDkPX6hvpGBkZmEKYpEtsMwTY2gLBBmoxATkNoAjKNYWxjJAOMEZpiAQ==", 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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Setup Start", type date}, {"Setup Complete", type date}, {"Blueprint Start", type date}, {"Blueprint Complete", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"ID"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Category"}, {"Value", "Date"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Sub-Category", each if Text.Contains([Category], "Blueprint") then "2 - Blueprint" else "1 - Workspace Setup")
in
    #"Added Conditional Column"