Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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"
Hi @Anonymous ,
Try this custom column:
Table.Transpose(Table.FromList({[Investment Type], [Investment Amount]}, Splitter.SplitTextByDelimiter(";")))
Thanks! It works! Awesome.
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"