Forum Discussion
How to use list.range function dynamically?
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'?
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
1 Reply
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