Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

power query split column by variable positions

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  Splitt...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"

     

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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"