Forum Discussion
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 | 117 | 2264 | 1631 | 4304 | 3418 | 3605 | 4123 | 0 | 0 | 0 |
| E,F,G | 0 | 0 | 0 | 0 | 0 | 0 | 3558 | 3008 | 2613 | 2617 | 4813 | 188 |
| H-K | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 602 | 1204 | 1011 | 2699 |
| L | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 380 |
While splitting column 'Category' by delimiter '-' I want that H-K should be split into H,I,J,K into different rows. How shall I do that?
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"
1 Reply
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"