cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cdcphist
Frequent Visitor

Dynamic table based on split column values

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):

 

1313,2,1
1442,1,3
1561,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:

 

131
414
165
736

 

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.

 

 

1 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"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

3 REPLIES 3
amitchandak
Super User IV
Super User IV

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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.

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"

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors