Forum Discussion
Unpivoting multiple columns
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.
MarcelBeugI will later tonight after work, thank you so much. The columns with duplicate names are a result of my mistake in not renamining them, they should have 1,2,3,etc following the names. The modifier columns should also follow the naming convention, sorry.
If I'm reading your post correctly, there are multiple queries which reference each other, as opposed to just one large query?
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.