Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

How to split by delimiter '-' in contiguous manner?

I have table :   Category January February March April May June July August September October November December A-B 2887 570 1782 3633 0 0 0 0 0 0 0 0 C-D 0 0 11...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous 

     

    Here is one way, you can get the list, then expand to new rows. Paste all in the Advanced Editor via blank query

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctR1UorViVZy1nUB0646bjruYJaHrjeY9lGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [a=Text.SplitAny([Category],"-,"),
b={List.Min(a)..List.Max(a)}][b])
in
    #"Added Custom"

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous 

 

Here is one way, you can get the list, then expand to new rows. Paste all in the Advanced Editor via blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctR1UorViVZy1nUB0646bjruYJaHrjeY9lGKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Category = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Category", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [a=Text.SplitAny([Category],"-,"),
b={List.Min(a)..List.Max(a)}][b])
in
    #"Added Custom"