How to use list.range function dynamically?

[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

I want output of category column like containing rows of H,I,J,K as well when i use split column with delimeters.

How do I do the same dynamically?

How can i use list.range function with text.beforedelimiter and text.afterdelimiter to get rows of H,I,J,K as well in column 'Category'?

[artemus · Accepted solution]

The goal here is to change the Category column into a list, then use expand list column to get copies of rows for each category.

Looking at this, there appears to be 3 kinds of categoreies:

• Single letters like "L"
• Enumerated letters like "E,F,G"
• A range of letters like "H-K"

To do this you will need to create a custom column that produces a list of categories:

= if Text.Length([Category] = 1 then
   [Category]
else if Text.At([Category], 1) = "-" then
   {Text.At([Category], 0) .. Text.At([Category], 2)}
else if Text.Contains([Category], ",") then
   Text.Split([Category], ",")
else
   error "Unknown category format"

Note that the .. operator only works for single letter text values.

After this, just remove the origional category column, and use "Expand list column" on your new column