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.
Hello,
One way to accomplish this:
- Select the first 23 columns and Unpivot Other Columns
- Add an index Column
- Add a custom Column with the formula =Number.Mod([Index],25)
- Add another custom Column with the formula = Number.IntegerDivide([Index],25)+1
- Remove the Index and Attribute columns
- select the column created in step 3 and Pivot Column - Select the Value column for the values and in the addvanced section select don't aggregate
You will now need to edit the headers that are showing as the numbers 0 through 24 to the correct headers. (you could also make a seperate table with the numbers 0 through 24 and the headers and join that table on the column created in 3 and use this new column in the pivot step)
Actually,
thinking about it the formula in step 4 should be =Number.Mod(Number.IntegerDivide([Index],25),7)+1 so that it cycles from 1 to 7 for each record
MarkS Unfortunatley this case is far more complicated as there are variable numbers of columns per group.
ElliotP The key to the solution here is to split the column names in a fixed part (which will be the new column names) and the variable part (which will become the row identifiers).
You can of course work with a fixed list of the fixed parts, but I don't think that would be a good idea:
there are some inaccuracies already ("Item Name1DiscountAmount" with a space, if "Sales1" is the fixed part then you'll never get a match for "Sales2", I guess "Sales1" should be "Sales")
and moreover I suspect the list won't be fixed over time.
The other approach would be to specify the exact rules how the actual column names can be split into a fixed part and a variable part. My first idea was to take out the digits 0-9, but unfortunately there are also fixed parts that include digits.
These specifications must be very precise, otherwise they can't be automated, so you need to eloborate on your phrase "same column names with subtle difference of number for grouping".
In my view this case is now beyond the kind of support you can expect from a free forum.
MarkSThanks for the response, I really appreciate it. Is the value (25) in step four and five dependent upon the number of columns under consideration? As in, can I slide it up if there are slightly more?
MarcelBeugThanks for the response. The list is fixed and has a fixed number of parts (though some row values may be null which can be easily removed with power query).
Sales1, Sales2, Sales3, etc would become Sales for example. The same would go for each column to accomodate the 7 possible row variations.
The list is fixed and will not increase or decrease over time. It will remain groups of seven identical packages if you will.
I don't mind if we can change it from 'Sales1' to 'SalesA' if you think that's accomodative, that was purely an easy organisational thing.
Sorry for not being clear in regards to "same column names with subtle difference of number of grouping". What I mean, is that there are for each transaction 7 identical groupings of the ItemName about the Item and the 5 Modifier groups of 3. In essence,
ItemNameNotes ItemNameQuantity ItemName ItemNameCategory ItemNameSku ItemNameItemID ItemNameVariationID Item Name1DiscountAmount Sales1 ItemNameDiscountAmount &
ItemNameModifier1Name ItemNameModifier1Amount ItemNameModifier1ID list_itemizations_itemization_type ItemNameModifier2Name ItemNameModifier2Amount ItemNameModifier2ID ItemNameModifier3Name ItemNameModifier3Amount ItemNameModifier3ID ItemNameModifier4Name ItemNameModifier4Amount ItemNameModifier4ID ItemNameModifier5Name ItemNameModifier5Amount ItemNameModifier5ID Will be the same, but there will be 7 combinations of both.
I agree, this is a complicated solution and one which is well beyond that of a free forum and a community that gives advice/solutions for free. I greatly appreciate the community & especially MarcelBeug for your incredible solutions in the past and the beyond amazing solution to the UTC time zone swapping issue. If we can find a solution that would be ideal, but I understand and appreciate everything.
