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.
Specializing in Power Query Formula Language (M)
