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

[Anonymous]

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?

[Vera_33 · Accepted solution]

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"