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.

ElliotP's avatar
ElliotP
Post Prodigy
9 years ago

Updated to include pbix and excel file.

  • MarcelBeug's avatar
    MarcelBeug
    Community Champion
    9 years ago

    I know nothing about API, data factory, Azure Blobs, Azure tables or modifiers.

    A phrase like "a transaction with 4 items and 2,0,0 and 1 modifier applied to the items" is complete abacadabra to me.

     

    What I would expect from your description, is 2 fields - TransactionID and DateTime - plus a mulitple of 7 fields, each with the same name except for a number that identifies each group of 7 fields.

     

    Instead, in your example there are not 2 but 109 (!) fields without digits in the field name.

     

    So I won't be able to help you.

    Maybe if you come up with a far better explanation and realistic examples of the required transformations.

     

    For instance, how should the result look like, for all fields with number 1 in the column name:

    • ElliotP's avatar
      ElliotP
      Post Prodigy
      9 years ago

      MarcelBeugUpdated Original Post to be a bit more clear. I've included what I would like to achieve, an example, an excel file demonstrating as well as a pbix. I cleaned up the headings and removed the unneccessary ones so there aren't as many columns.