cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Split 2 multi-value columns by deliminator and transpose them into rows

Hi,

 

I have 2 columns with multiple value but have a consistent number of multiple values for each row in each column. If this were just one column with multiple-values, I could use the Split-to-row function immediately. However, since there are 2 columns with multiple values, I had to duplicate the tables and do it independently and merge them after. I am not in favor of this solution duplicating them creates large file size. Hoping to see if there is an elegant solution to this. 

 

Thanks in advance!

-Ayfiq

 

Example below:

From this: 
NameInvestment TypeInvestment Amount
RonaldStocks;Gold;Bond100;300;400
JacobFD;Stocks200;500
   
To this:  
NameInvestment TypeInvestment Amount
RonaldStocks100
RonaldGold300
RonaldBond400
JacobFD200
JacobStocks500
2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Split 2 multi-value columns by deliminator and transpose them into rows

Hi @ayfiq 

 

Sure, you can add a custom column like in the script below, or see the attached for the reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsrPS8xJUdJRCi7JT84utnbPz0mxdsrPAwkZGhhYGwOxiYGBUqxOtJJXYnJ+ElDczcUaohrINgLKm4LkYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Investment Type" = _t, #"Investment Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Investment Type", type text}, {"Investment Amount", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Investment", each let 
            Type = Text.Split( [Investment Type], ";" ),
            Amount = Text.Split( [Investment Type], ";" ),
            Count = List.Max( { List.Count( Type ), List.Count( Amount ) } ) -1,
            Transform = List.Transform( 
                { 0.. Count }, 
                ( i ) => [Type = Type{i}?, Amount = Amount{i}?]
            ) 
        in 
            Transform),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Investment Type", "Investment Amount"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Investment"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Investment", {"Type", "Amount"}, {"Investment.Type", "Investment.Amount"})
in
    #"Expanded Custom1"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

Highlighted
Community Champion
Community Champion

Re: Split 2 multi-value columns by deliminator and transpose them into rows

Hi @ayfiq ,

 

Try this custom column:

 

Table.Transpose(Table.FromList({[Investment Type], [Investment Amount]}, Splitter.SplitTextByDelimiter(";")))

 

Capture.PNGCapture1.PNG

 

 


Did I answer your question? Mark my post as a solution!
Ricardo

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Split 2 multi-value columns by deliminator and transpose them into rows

Hi @ayfiq 

 

Sure, you can add a custom column like in the script below, or see the attached for the reference.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCsrPS8xJUdJRCi7JT84utnbPz0mxdsrPAwkZGhhYGwOxiYGBUqxOtJJXYnJ+ElDczcUaohrINgLKm4LkYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Investment Type" = _t, #"Investment Amount" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Investment Type", type text}, {"Investment Amount", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Investment", each let 
            Type = Text.Split( [Investment Type], ";" ),
            Amount = Text.Split( [Investment Type], ";" ),
            Count = List.Max( { List.Count( Type ), List.Count( Amount ) } ) -1,
            Transform = List.Transform( 
                { 0.. Count }, 
                ( i ) => [Type = Type{i}?, Amount = Amount{i}?]
            ) 
        in 
            Transform),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Investment Type", "Investment Amount"}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Investment"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Investment", {"Type", "Amount"}, {"Investment.Type", "Investment.Amount"})
in
    #"Expanded Custom1"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

View solution in original post

Highlighted
Community Champion
Community Champion

Re: Split 2 multi-value columns by deliminator and transpose them into rows

Hi @ayfiq ,

 

Try this custom column:

 

Table.Transpose(Table.FromList({[Investment Type], [Investment Amount]}, Splitter.SplitTextByDelimiter(";")))

 

Capture.PNGCapture1.PNG

 

 


Did I answer your question? Mark my post as a solution!
Ricardo

View solution in original post

Highlighted
New Member

Re: Split 2 multi-value columns by deliminator and transpose them into rows

Thanks! It works! Awesome.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors