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

[Anonymous]

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:
Name	Investment Type	Investment Amount
Ronald	Stocks;Gold;Bond	100;300;400
Jacob	FD;Stocks	200;500
To this:
Name	Investment Type	Investment Amount
Ronald	Stocks	100
Ronald	Gold	300
Ronald	Bond	400
Jacob	FD	200
Jacob	Stocks	500

[Mariusz · Accepted solution]

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

[camargos88 · Accepted solution]

Hi @Anonymous ,

Try this custom column:

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

[Anonymous]

Thanks! It works! Awesome.

[Mariusz · Accepted solution]

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