The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
140 | |
106 | |
105 | |
74 | |
61 |
User | Count |
---|---|
266 | |
127 | |
119 | |
100 | |
88 |