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

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"

Hi Anonymous ,

Try this custom column:

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