Dynamic table based on split column values

[cdcphist]

Hi,

I have the following table, where the last column represents a list of column numbers separated by commas (a more simple example than what i really have):

1	3	1	3,2,1
1	4	4	2,1,3
1	5	6	1,3,2
3	6	7	3,1,2

How can i take the last column and use this to reorder the 3 other columns after i load the data? What i need to end up is a table like so:

1	3	1
4	1	4
1	6	5
7	3	6

I know that i can use the split function to turn this last column into a number of columns but i have no idea of what to do next.

Thanks.

[amitchandak]

@cdcphist , I would suggest solving such a problem outside power bi.  But In power query, you can do something.

Let us check with an expert

@ImkeF , can you help with this

[cdcphist]

It's not an option, as any methods for that would invalidate showing the report in realtime. So, ideally, it would need to be done in PowerBI.

Thanks.

[ImkeF · Accepted solution]

Hello @cdcphist ,

this is a dynamic solution, just make sure to adjust the colum name with the list order (here: "Column4"):

let
    Source = Table.FromRows(
        Json.Document(
            Binary.Decompress(
                Binary.FromText(
                    "i45WMlTSUTIGYjCtY6RjqBSrAxE1gWKgmI4xXNQUiM1A6nWAqsGixlARc7AJhiDRWAA=",
                    BinaryEncoding.Base64
                ),
                Compression.Deflate
            )
        ),
        let
            _t = ((type nullable text) meta [Serialized.Text = true])
        in
            type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]
    ),
    #"Added Custom" = Table.AddColumn(
        Source,
        "ListOfPositions",
        each List.Transform(Text.Split([Column4], ","), Number.From)
    ),
    #"Added Custom1" = Table.AddColumn(
        #"Added Custom",
        "Custom",
        each List.Transform([ListOfPositions], (x) => Record.FieldValues(_){x - 1})
    ),
    #"Removed Other Columns" = Table.FromRows(
        Table.SelectColumns(#"Added Custom1", {"Custom"})[Custom]
    )
in
    #"Removed Other Columns"