Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Dynamically pivot data table according to incidence of repeats

    Posted Jan 07, 2022 12:17 PM
    Edited by Leonardo Valenca Jan 07, 2022 12:18 PM
    I have the data table partly pictured below…

    As highlighted, there are a few instances of repeated "ID parcela pendente" records due to having more than one "ID vendedor" related to it.

    What I'd like to do is to PIVOT that table so as many columns are added as there are "ID vendedor" repeats, i.e.: ID vendedor 1, ID vendedor 2, and so on.

    Something like the table below:

    However, there are some points of contention that I need to adhere to:

    • The null values need to stay as is (i.e.: those "ID parcela pendente" with no related "ID vendedor" should display null on all columns that are to be created).
    • This example is a simple one and only shows a couple of repeated "ID vendedor" instances. But this is a dynamic scenario, and there may be cases as the data set evolves where a new "ID parcela pendente" might have 3, 4 repeated "ID vendedor", and I need the query to pick that up and add more columns as needed.
    • And of course, those "ID parcela pendente" with no repeats shall display null under the new "ID vendedor 2" column as well as any additional columns that need to be created in the future to attend to additional "ID vendedor" repeats that might show up in the future (just as shown on the above sample table).

    Am I asking too much here? I don't even know where to begin…

    Thanks in advance for any help on this one.

    Leonardo Valenca

  • 2.  RE: Dynamically pivot data table according to incidence of repeats
    Best Answer

    Posted Jan 10, 2022 02:28 AM
    The technique I suggest using for this:
    • Group by "ID parcela pendente" - Table.Group() or context menu
    • Combine with a delimiter (e.g. "|")
    • Split Columns by Delimiter - context menu option
    Here's the example PQ code:
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyVtJRilCK1YlWcnF1Q2NHIrGjwGx3D08gG8z08vaBC/v6+cOEAwKD4KZA2BAlwSGhEHYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Store = _t, Vendor = _t]),
        #"Grouped Rows" = Table.Group(Source, {"Store"}, {{"Vendors Grouped", each _, type table [Store=nullable text, Vendor=nullable text]}}),
        #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Vendor", each Text.Combine([Vendors Grouped][Vendor],"|")),
        #"Replaced Value" = Table.ReplaceValue(#"Added Custom","",null,Replacer.ReplaceValue,{"Vendor"}),
        #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value", "Vendor", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Vendor.1", "Vendor.2", "Vendor.3"})
        #"Split Column by Delimiter"​

    You could certainly add steps to clean up duplicate vendors, dynamically generate the vendor column names and grow/shrink the number of columns.

    Lan Huynh
    Data Visualisation Developer

  • 3.  RE: Dynamically pivot data table according to incidence of repeats

    Posted Jan 10, 2022 03:52 PM
    That for sure got things done the way I needed.

    Thanks a bunch!

    Leonardo Valenca