Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

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

camargos88
Community Champion
Community Champion

Hi @Anonymous ,

 

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!

Proud to be a Super User!



View solution in original post

3 REPLIES 3
camargos88
Community Champion
Community Champion

Hi @Anonymous ,

 

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!

Proud to be a Super User!



Anonymous
Not applicable

Thanks! It works! Awesome.

Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors