power query split column by variable positions

[Anonymous]

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

[Vijay_A_Verma · Accepted 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"

[Anonymous]

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"