Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Community
I need to split Column1 based on variable postions from Custom.1 column. Same postion values stored as a list in Custom.3 column
I have tried using the list as an argument in Splitter.SplitTextByPositions but it didn't work
Thanks in advance
Column1 | Custom.1 |
A B C | 0;2;4 |
4 5 6 | 0;2;4 |
A B C | 0;8;16 |
1 2 3 | 0;8;16 |
A B C | 0;3;8 |
11 22 33 | 0;3;8 |
Solved! Go to Solution.
Use below formula in a custom column
= Splitter.SplitTextByPositions(List.Transform(Text.Split([Custom.1],";"),each Number.From(_)))([Column1])
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclRwUnBW0lEysDayNlGK1YlWMlEwVTBDEUGosbA2NAMLGSoYKRijCiFUGVtbQBQBVQGVGaMIOjo51xgaGZuYQkWBVsQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Custom.1 = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Splitter.SplitTextByPositions(List.Transform(Text.Split([Custom.1],";"),each Number.From(_)))([Column1]))
in
#"Added Custom"
Use below formula in a custom column
= Splitter.SplitTextByPositions(List.Transform(Text.Split([Custom.1],";"),each Number.From(_)))([Column1])
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclRwUnBW0lEysDayNlGK1YlWMlEwVTBDEUGosbA2NAMLGSoYKRijCiFUGVtbQBQBVQGVGaMIOjo51xgaGZuYQkWBVsQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Custom.1 = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Splitter.SplitTextByPositions(List.Transform(Text.Split([Custom.1],";"),each Number.From(_)))([Column1]))
in
#"Added Custom"
Thanks, it worked on my data but not from your Source, may be because it removed some blanks
However, I modified it to CSV rather than JSON as below
Thanks again
let
Source = Csv.Document(
"Column1,Custom.1
A B C,0;2;4
4 5 6,0;2;4
A B C,0;8;16
1 2 3,0;8;16
A B C,0;3;8
11 22 33,0;3;8",2,","),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Added Custom" = Table.AddColumn(#"Promoted Headers", "Custom", each Splitter.SplitTextByPositions(List.Transform(Text.Split([Custom.1],";"),each Number.From(_)))([Column1])),
#"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom", each Text.Combine(List.Transform(_, Text.From), ":"), type text})
in
#"Extracted Values"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.