Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi, I have a long list of patients that I want to split into multiple columns. I can export the list to excel and do it manually but there must be a way in powerbi. Thank you so much for your help.
Solved! Go to Solution.
Hi @Naz ,
I have created a sample for your reference. Please check the following steps as below.
1. Insert an index column in power query for the fact table.
2. Then created a calculatted column based on that.
index 2 =
VAR inde =
INT ( 'Table'[Index] / 10 )
RETURN
IF ( 'Table'[Index] < 10, 'Table'[Index], 'Table'[Index] - inde * 10 )
3. Then we can get the excepted table by the formula.
Table 3 =
VAR gen =
GENERATESERIES ( 0, 9, 1 )
RETURN
ADDCOLUMNS (
gen,
"1",
VAR minindex =
CALCULATE ( MIN ( 'Table'[Index] ), FILTER ( 'Table', 'Table'[index 2] = 0 ) )
RETURN
CALCULATE (
MAX ( 'Table'[Patient] ),
FILTER ( 'Table', 'Table'[index 2] = [Value] && 'Table'[Index] <= minindex )
),
"2", CALCULATE (
MAX ( 'Table'[Patient] ),
FILTER (
'Table',
'Table'[index 2] = [Value]
&& 'Table'[Index] <= 20
&& 'Table'[Index] > 10
)
),
"3", CALCULATE (
MAX ( 'Table'[Patient] ),
FILTER (
'Table',
'Table'[index 2] = [Value]
&& 'Table'[Index] <= 30
&& 'Table'[Index] > 20
)
),
"4", CALCULATE (
MAX ( 'Table'[Patient] ),
FILTER (
'Table',
'Table'[index 2] = [Value]
&& 'Table'[Index] <= 40
&& 'Table'[Index] > 30
)
)
)
For more details, please check the pbix as attached.
Hi @Naz ,
I have created a sample for your reference. Please check the following steps as below.
1. Insert an index column in power query for the fact table.
2. Then created a calculatted column based on that.
index 2 =
VAR inde =
INT ( 'Table'[Index] / 10 )
RETURN
IF ( 'Table'[Index] < 10, 'Table'[Index], 'Table'[Index] - inde * 10 )
3. Then we can get the excepted table by the formula.
Table 3 =
VAR gen =
GENERATESERIES ( 0, 9, 1 )
RETURN
ADDCOLUMNS (
gen,
"1",
VAR minindex =
CALCULATE ( MIN ( 'Table'[Index] ), FILTER ( 'Table', 'Table'[index 2] = 0 ) )
RETURN
CALCULATE (
MAX ( 'Table'[Patient] ),
FILTER ( 'Table', 'Table'[index 2] = [Value] && 'Table'[Index] <= minindex )
),
"2", CALCULATE (
MAX ( 'Table'[Patient] ),
FILTER (
'Table',
'Table'[index 2] = [Value]
&& 'Table'[Index] <= 20
&& 'Table'[Index] > 10
)
),
"3", CALCULATE (
MAX ( 'Table'[Patient] ),
FILTER (
'Table',
'Table'[index 2] = [Value]
&& 'Table'[Index] <= 30
&& 'Table'[Index] > 20
)
),
"4", CALCULATE (
MAX ( 'Table'[Patient] ),
FILTER (
'Table',
'Table'[index 2] = [Value]
&& 'Table'[Index] <= 40
&& 'Table'[Index] > 30
)
)
)
For more details, please check the pbix as attached.
Very smart!! How come a simple job like this has to be done in such a complicated way? Unbelievable!! Thank you so much for the codes.
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
72 | |
38 | |
31 | |
27 |
User | Count |
---|---|
92 | |
50 | |
44 | |
40 | |
35 |