Forum Discussion
Multiple Rows transpose to Column
Hi,
How can I unpivot 1 & 2 row to columns?
e.g.
1st row value transpose to 1st column
2nd row value transpose to 2nd column
if it was only 1 row-level header then it would have been easy just unpivotting, but when we have two-layed headers,
in this case any simple way?
Honestly, there's a lot going on in your M code—way more than what was initially asked. If I were applying the code I shared across multiple files (assuming the sources are Excel workbooks), I’d first connect to each file using Excel.Workbook in a custom column, expand that column, select the needed sheets or tables, and then apply the logic I gave you—only to the actual data, not the Excel binary files.
6 Replies
Hi Anonymous
Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdDNasMwDADgVwk5j1Y/lmQd2127Ugo7lTLClkMhXUeadnv8eYkTGA0EYkv2Z0mHQ7mr29Plo7h2VdsV3elcl0/lbvOyLbZVv36um6ao7tWpKfb/t/XPe3Mr1pvXMfz13RbX6l4X5wT2hy+3z65up+vHp0OZwunb7t8EBVbTkvISKcR1jpqu+isQlohLApJcW/o5LQIaYcC/oPsCJZIRhLTTRYDgKh51yFkMbBNFMxQRRYNMERimiqSn2DW9o8pDLoI6IfdYsnjGYtbgY1VK5CA2UAJmwBaHnEcmURupMENpyovnqhAj4EQZBkYb+xMiwFGSR0kEQMjGBtNoMSpmyt0YKPfnwVnjSOkjpR5TGyGfZzYmNx8o1nRcp7ETR5gom6lKVYNhHi0xBXDJFFpUMxkpN1TQ8nj8BQ==", 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, Column6 = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Column1", "Column2"}), RenameValues = let tbl = #"Replaced Value", Headers = Table.FirstN(tbl,2), Transposed = Table.Transpose(Headers), AddedIndex = Table.AddIndexColumn(Transposed, "Index", 1), CurrentHeaders = Table.AddColumn(AddedIndex, "Original", each "Column" & Text.From([Index])), NewNames = Table.AddColumn(CurrentHeaders, "New Names", each Text.Combine({[Column1],[Column2]}, "__")), RenameValues = List.Zip({NewNames[Original],NewNames[New Names] }) in RenameValues, #"Automatically Renamed Headers" = Table.RenameColumns( #"Replaced Value",RenameValues), #"Removed Top Rows" = Table.Skip(#"Automatically Renamed Headers",2), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Top Rows", {"Period start time", "PLMN Name"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter({"__"}, QuoteStyle.Csv, false), {"Attribute.1", "Attribute.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Period start time", type date}, {"PLMN Name", type text}, {"Attribute.1", type text}, {"Attribute.2", type text}, {"Value", type number}}) in #"Changed Type"Hi,
I tried with your suggestion but a bit difficult to migrate for me.
can you pls rewrite it with my code?
data value shown from the line in orange.
let
Source = SharePoint.Contents("https://XXX.sharepoint.com/sites/XXX", [ApiVersion = 15]),
#"Shared Documents" = Source{[Name="Shared Documents"]}[Content],
#"KR Cell Status" = #"Shared Documents"{[Name="KR Cell Status"]}[Content],
#"Filtered Rows CT" = Table.SelectRows(#"KR Cell Status", each [Name] = "KT" or [Name] = "SKT" or [Name] = "LGU+"),#"Listed Columns CT" = Table.ColumnNames(#"Filtered Rows CT"[Content]{0}),
#"Expanded Custom CT" = Table.ExpandTableColumn(#"Filtered Rows CT", "Content", #"Listed Columns CT", List.Transform(#"Listed Columns CT", each "Custom/" & _)),
#"Filtered Rows PM Data - KPI Trend" = Table.SelectRows(#"Expanded Custom CT", each ([#"Custom/Name"] = "PM Data - KPI Trend")),
#"Listed Columns PM Data - KPI Trend" = Table.ColumnNames(#"Filtered Rows PM Data - KPI Trend"[#"Custom/Content"]{0}),
#"Expanded Custom PM Data - KPI Trend" = Table.ExpandTableColumn(#"Filtered Rows PM Data - KPI Trend", "Custom/Content", #"Listed Columns PM Data - KPI Trend", List.Transform(#"Listed Columns PM Data - KPI Trend", each "Custom." & _)),
#"Filtered Rows <> null" = Table.SelectRows(#"Expanded Custom PM Data - KPI Trend", each ([Custom.Name] <> null)),
#"Listed Columns File" = Table.ColumnNames(#"Filtered Rows <> null"[Custom.Content]{0}),
#"Expanded Custom File" = Table.ExpandTableColumn(#"Filtered Rows <> null", "Custom.Content", #"Listed Columns File", List.Transform(#"Listed Columns File", each "Custom-" & _)),
#"Removed Other Columns" = Table.SelectColumns(#"Expanded Custom File",{"Custom-Content", "Custom-Name", "Custom-Date created", "Custom.Name", "Name"}),
#"Added Year" = Table.AddColumn(#"Removed Other Columns", "Year", each Text.Start([#"Custom-Name"], 4)),
#"Added Week" = Table.AddColumn(#"Added Year", "Week", each Text.Middle([#"Custom-Name"], 6, 2)),
#"Added Period" = Table.AddColumn(#"Added Week", "Period", each [Year] & [Week]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Period",{{"Custom-Date created", type date}, {"Week", Int64.Type}, {"Period", Int64.Type}, {"Year", Int64.Type}}),
#"Sorted Rows Descending Period" = Table.Sort(#"Changed Type",{{"Period", Order.Descending}}),
#"Distinct Period" = Table.Distinct(Table.SelectColumns(#"Sorted Rows Descending Period", {"Period"})),
#"Top N= 104 Period" = Table.FirstN(#"Distinct Period", 104),
#"Filtered Rows Period >= 25R1 Profile Data" = Table.SelectRows(#"Top N= 104 Period", each [Period] >= 202511),
#"Joined Period and File" = Table.NestedJoin(#"Sorted Rows Descending Period", {"Period"}, #"Filtered Rows Period >= 25R1 Profile Data", {"Period"}, "Period Mapping", JoinKind.Inner),
#"Added Weekly" = Table.AddColumn(#"Joined Period and File", "Weekly", each if [Year] = Date.Year(DateTime.LocalNow()) and [Week] >= Date.WeekOfYear(DateTime.LocalNow()) - 4 then "Keep" else "Remove"),
#"Added Monthly" = Table.AddColumn(#"Added Weekly", "Monthly", each if [Week] = 5 or [Week] = 9 or [Week] = 13 or [Week] = 18 or [Week] = 22 or [Week] = 26 or [Week] = 31 or [Week] = 35 or [Week] = 39 or [Week] = 44 or [Week] = 48 or [Week] = 52 then "Keep" else "Remove"),
#"Merged Weekly Monthly" = Table.AddColumn(#"Added Monthly", "Weekly/Monthly", each [Weekly] & " " & [Monthly]),
#"Split Column by Delimiter Frequency | Region" = Table.SplitColumn(#"Merged Weekly Monthly", "Custom.Name", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Custom.Name.1", "Custom.Name.2"}),
#"Filtered Rows <> Remove Remove" = Table.SelectRows(#"Split Column by Delimiter Frequency | Region", each ([#"Weekly/Monthly"] <> "Remove Remove")),
#"Add Custom Table for Sheet"= Table.AddColumn(#"Filtered Rows <> Remove Remove", "Custom", each Excel.Workbook([#"Custom-Content"])),
#"Listed Columns Sheet" = Table.ColumnNames(#"Add Custom Table for Sheet"[Custom]{0}),
#"Expanded Custom Sheet" = Table.ExpandTableColumn(#"Add Custom Table for Sheet", "Custom", #"Listed Columns Sheet", List.Transform(#"Listed Columns Sheet", each "Custom/" & _)),
#"Filtered Rows 1st Sheet Contains ""System""" = Table.SelectRows(#"Expanded Custom Sheet", each Text.Contains([#"Custom/Name"], "System") or Text.Contains([#"Custom/Name"], "system")),
#"Filtered Rows LTE" = Table.SelectRows(#"Filtered Rows 1st Sheet Contains ""System""", each ([Custom.Name.1] = "LTE")),
#"Listed Columns Value" = Table.ColumnNames(#"Filtered Rows LTE"[#"Custom/Data"]{0}),
#"Expanded Custom Value" = Table.ExpandTableColumn(#"Filtered Rows LTE", "Custom/Data", #"Listed Columns Value", List.Transform(#"Listed Columns Value", each "Custom^" & _)),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom Value",{"Custom-Content", "Period Mapping", "Custom/Item", "Custom/Kind", "Custom/Hidden"}),
#"Transposed Table" = Table.Transpose(#"Removed Columns"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Transposed Table", {{"Column1", type text}, {"Column2", type text}}, "en-US"),{"Column1", "Column2"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged"),
#"Re-transposed Table" = Table.Transpose(#"Merged Columns"),
#"Promoted Headers" = Table.PromoteHeaders(#"Re-transposed Table", [PromoteAllScalars=true]),
#"Renamed Columns Fixed Header" = Table.RenameColumns(#"Promoted Headers",
{{Table.ColumnNames(#"Promoted Headers"){0}, "Source Name"},
{Table.ColumnNames(#"Promoted Headers"){1}, "Date Created"},
{Table.ColumnNames(#"Promoted Headers"){2}, "Frequency"},
{Table.ColumnNames(#"Promoted Headers"){3}, "Region"},
{Table.ColumnNames(#"Promoted Headers"){4}, "CT"},
{Table.ColumnNames(#"Promoted Headers"){5}, "Year"},
{Table.ColumnNames(#"Promoted Headers"){6}, "Week"},
{Table.ColumnNames(#"Promoted Headers"){7}, "Period"},
{Table.ColumnNames(#"Promoted Headers"){8}, "Last 5 Weeks"},
{Table.ColumnNames(#"Promoted Headers"){9}, "Last Week of Month"},
{Table.ColumnNames(#"Promoted Headers"){10}, "Keep or Remove"},
{Table.ColumnNames(#"Promoted Headers"){11}, "Sheet Name"},
{Table.ColumnNames(#"Promoted Headers"){12}, "Period start time"},
{Table.ColumnNames(#"Promoted Headers"){13}, "PLMN Name"}}),
#"Filtered Rows Removed Duplicate Header" = Table.SelectRows(#"Renamed Columns Fixed Header", each ([#"Period start time"] <> "" and [#"Period start time"] <> "Period start time")),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows Removed Duplicate Header", {"Source Name", "Date Created", "Frequency", "Region", "CT", "Year", "Week", "Period", "Last 5 Weeks", "Last Week of Month", "Keep or Remove", "Sheet Name", "Period start time", "PLMN Name"}, "Attribute", "Value"),
#"Split Column by Delimiter KPI Alias | KPI ID" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
#"Renamed Columns KPI Alias | KPI ID" = Table.RenameColumns(#"Split Column by Delimiter KPI Alias | KPI ID",{{"Attribute.1", "KPI Alias"}, {"Attribute.2", "KPI ID"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns KPI Alias | KPI ID",{{"Date Created", type date}, {"Period start time", type date}, {"Value", type number}, {"Year", Int64.Type}, {"Week", Int64.Type}, {"Period", Int64.Type}})
in
#"Changed Type1"Honestly, there's a lot going on in your M code—way more than what was initially asked. If I were applying the code I shared across multiple files (assuming the sources are Excel workbooks), I’d first connect to each file using Excel.Workbook in a custom column, expand that column, select the needed sheets or tables, and then apply the logic I gave you—only to the actual data, not the Excel binary files.
Hello Anonymous
try this
Select the top two header rows (first two rows), and Transpose the table (Home → Transform → Transpose).
Now you will see two columns:
Column1 → Previous first-row header (Fruits, Fruits, Snacks, Snacks)
Column2 → Previous second-row header (Apple, banana, Chips, Popcorn)
Select both these columns and Fill Down/Fill Up if needed.
Select these two columns, and then the rest (the value columns), and Unpivot Other Columns.
Rename
Attribute → Value
The other two columns → Category1, Category2
Thanks,
Pankaj Namekar | LinkedInIf this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.
Hi,
Try to use Table.Transpose M function.
Table.Transpose - PowerQuery M | Microsoft Learn
Please check the below picture and the attached pbix file.
let Source = source, #"Transposed Table" = Table.Transpose(Source) in #"Transposed Table"
