Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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'?
Solved! Go to 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:
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
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.