Forum Discussion

ElliotP's avatar
ElliotP
Post Prodigy
9 years ago
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...
Need Help
Show and Tell
Tutorial Requests
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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.

MarcelBeug's avatar
MarcelBeug
Community Champion
9 years ago

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.

 

ElliotP's avatar
ElliotP
Post Prodigy
9 years ago

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,

ItemNameNotesItemNameQuantityItemNameItemNameCategoryItemNameSkuItemNameItemIDItemNameVariationIDItem Name1DiscountAmountSales1ItemNameDiscountAmount

&

ItemNameModifier1NameItemNameModifier1AmountItemNameModifier1IDlist_itemizations_itemization_typeItemNameModifier2NameItemNameModifier2AmountItemNameModifier2IDItemNameModifier3NameItemNameModifier3AmountItemNameModifier3IDItemNameModifier4NameItemNameModifier4AmountItemNameModifier4IDItemNameModifier5NameItemNameModifier5AmountItemNameModifier5ID

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.