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.
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.
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.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |