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

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors