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"
raisurrahman's avatar
raisurrahman
Icon for Helper II rankHelper II
11 months ago

third_hicana I think this is the required format.



Your issue is a classic Transpose–Fill Down pattern. However, there’s a small tweak needed — it’s about handling blank cells.

When you unpivot, Power Query automatically removes null values. To fix this:

  1.            Delete the “Changed Type” step that Power Query applies automatically. This keeps your date column as text.
  2.            Replace all nulls with a unique placeholder like ||*||.
  3.            Perform the unpivot.
  4.            Then replace ||*|| back with null.

Voilà — your Transpose–Fill Down pattern works perfectly.



let
    Source = Excel.CurrentWorkbook(){[Name="rng"]}[Content],
    #"Transposed Table" = Table.Transpose(Source),
    #"Filled Down" = Table.FillDown(#"Transposed Table",{"Column1"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Down",{"Column1", "Column2"},Combiner.CombineTextByDelimiter("||", QuoteStyle.None),"Merged"),
    #"Transposed Table1" = Table.Transpose(#"Merged Columns"),
    #"Promoted Header" = Table.PromoteHeaders(#"Transposed Table1"),
    #"Replaced Value" = Table.ReplaceValue(#"Promoted Header",null,"||*||",Replacer.ReplaceValue,{"||ID", "1 - Workspace Setup||Setup Start", "1 - Workspace Setup||Setup Complete", "2 - Blueprint||Blueprint Start", "2 - Blueprint||Review Complete"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"||ID"}, "Attribute", "Value"),
    #"Replaced Value1" = Table.ReplaceValue(#"Unpivoted Other Columns","||*||",null,Replacer.ReplaceValue,{"Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Value", type date}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "Attribute", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter1",{{"Value", "Date"}, {"Attribute.2", "Category"}, {"Attribute.1", "Sub-Category"}, {"||ID", "ID"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ID", "Category", "Sub-Category", "Date"})
in
    #"Reordered Columns"