Transpose/ Transform Rows to Columns

[third_hicana]

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.

[jgeddes · Accepted solution]

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]

@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"

[KarinSzilagyi]

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!

[wdx223_Daniel]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Custom1 = let a = Table.ToRows(Source) in #table({"ID","Category","Subcategory","Date"},List.TransformMany(List.Skip(a,2),each List.Transform(List.Skip(List.Positions(_)),(x)=>{a{1}{x},List.Last(List.RemoveNulls(List.FirstN(a{0},x+1))),_{x}}),(x,y)=>{x{0}}&y))
in
    Custom1

[m_dekorte]

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

[raisurrahman]

@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"

[v-veshwara-msft]

Hi @third_hicana ,

Thanks for reaching out to the Microsoft Fabric Community.

I wanted to check if the responses shared so far were helpful in resolving your query. If you need any additional assistance, please let us know.

Appreciate everyone’s contributions and for sharing valuable insights with the community.

Thank you.

[v-veshwara-msft]

Hi @third_hicana ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.