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"
KarinSzilagyi's avatar
KarinSzilagyi
Icon for Super User rankSuper User
11 months ago

Hi third_hicana, you need a combination of Transpose, Fill-down, Unpivot and some place-holders to achieve what you're trying to do.

Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TYyxDsIgEEB/hTBXcpxinNXFtR0cCENjbmisluDV/r5HI2hyA+/x7rzXutFWbdR1SvdX7G+kOuI5ipVB+TiOM8U0PDmr0Hh9OctrjVTHfeJKp+kRR2ISUZdq0tJ7oOXX5EtWPFgDaBDQZdgamQJoYFdg7fGbuGIR/sA6A/u6fKhnQ/gA", 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]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type"),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filled Down", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type text}, {"ID", type text}, {"1", type date}, {"2", type date}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type1",null,#date(1900, 1, 1),Replacer.ReplaceValue,{"1"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {"Column1", "ID"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"ID", "Category"}, {"Attribute", "ID"}, {"Column1", "Sub-Category"}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Renamed Columns",#date(1900, 1, 1),null,Replacer.ReplaceValue,{"Value"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value2",{"ID", "Category", "Sub-Category", "Value"})
in
    #"Reordered Columns"

Result:

 


Steps:
1) Make sure that your starting-table looks like this:

=> It's important that the row with the nulls and Sub-Category-names is row 1

2) Select Column1 and apply Transform > Transpose. This will give us the basis to fill down the Sub-Category names


3) Select Column1 and apply Transform > Fill > Fill down

 

4) Apply Transform > Use first Row as Headers to get the IDs into the header and adjust the datatypes as needed (double-check that the dates are still dates):

 

5) Replace the null-value in the date for the column "1" with a placeholder, e.g. "01.01.1900" => this is important to prevent the row from being filtered out when we unpivot the columns in a bit!


6) Select the columns "1" and "2" and apply Transform > Unpivot Columns

7) Replace the placeholder date for the null-value(s) (e.g. 01.01.1900) back to null

8.) Rename + Reorder Columns as needed + confirm data types => You're done!