Forum Discussion
Solved
Unpivoting multiple columns
Morning, Updated 9/6 I currently have a large number of rows which are the result of un-nesting from a json file. I would like to be unpivot two groups of rows as so instead of there simpl...
You can easily combine the different queries into 1:
let FixedParts = List.Transform({1..7}, each "Modifier"&Text.From(_))&{ "ItemName", "Item Name", "Notes", "Quantity", "Category", "Sku", "ItemID", "VariationID", "Sales", "DiscountAmount", "Discount", "Name", "Amount", "ID", "list_itemizations_itemization_type"}, Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Elliot 20170609\unpivotdatastructure.xlsx"), null, true), Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]), #"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","NULL",null,Replacer.ReplaceValue,Table.ColumnNames(#"Promoted Headers")), First23columnNames = List.FirstN(Table.ColumnNames(#"Replaced Value"),23), Unpivot = Table.UnpivotOtherColumns(#"Replaced Value", First23columnNames, "Attribute", "Value"), VariableParts = Table.AddColumn(Unpivot, "VariableParts", each List.Select(Splitter.SplitTextByAnyDelimiter(FixedParts)([Attribute]), each _ <> "")), #"Added Custom" = Table.AddColumn(VariableParts, "FixedPart", each Text.Combine(List.Select(Splitter.SplitTextByEachDelimiter([VariableParts])([Attribute]), each _ <> ""))), #"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"VariableParts", Text.Combine, type text}}), #"Removed Columns" = Table.RemoveColumns(#"Trimmed Text",{"Attribute"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[FixedPart]), "FixedPart", "Value"), #"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each [VariableParts] >= "1" and [VariableParts] <= "7") in #"Filtered Rows"Once your source data is correct, you probably won't need the last step anymore.
You can easily combine the different queries into 1:
let
FixedParts = List.Transform({1..7}, each "Modifier"&Text.From(_))&{
"ItemName",
"Item Name",
"Notes",
"Quantity",
"Category",
"Sku",
"ItemID",
"VariationID",
"Sales",
"DiscountAmount",
"Discount",
"Name",
"Amount",
"ID",
"list_itemizations_itemization_type"},
Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Elliot 20170609\unpivotdatastructure.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Replaced Value" = Table.ReplaceValue(#"Promoted Headers","NULL",null,Replacer.ReplaceValue,Table.ColumnNames(#"Promoted Headers")),
First23columnNames = List.FirstN(Table.ColumnNames(#"Replaced Value"),23),
Unpivot = Table.UnpivotOtherColumns(#"Replaced Value", First23columnNames, "Attribute", "Value"),
VariableParts = Table.AddColumn(Unpivot, "VariableParts", each List.Select(Splitter.SplitTextByAnyDelimiter(FixedParts)([Attribute]), each _ <> "")),
#"Added Custom" = Table.AddColumn(VariableParts, "FixedPart", each Text.Combine(List.Select(Splitter.SplitTextByEachDelimiter([VariableParts])([Attribute]), each _ <> ""))),
#"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"VariableParts", Text.Combine, type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Trimmed Text",{"Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[FixedPart]), "FixedPart", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each [VariableParts] >= "1" and [VariableParts] <= "7")
in
#"Filtered Rows"
Once your source data is correct, you probably won't need the last step anymore.
Evening,
Sorry for not getting back faster. I've spent the past few days working with the solution; it's amazing. Thank you so much, I greatly appreciate it. It works phenominally and has solved a major headache for me. MarcelBeug you again have come up with an incredible solution, thank you so much, you have no idea how it means to me.