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.
Dear All,
I have the following table:
CODE | GLOBAL VALUE | VALUE |
165 | 1.435,00 | 508,00 |
182 | 2.732,00 | 130,60 |
215 | 3.524,00 | 1.456,00 |
null | 2.844,00 | null |
259 | 3.269,00 | 2.889,31 |
343 | 4.589,00 | 590,14 |
null | 1.556,00 | null |
362 | 3.925,00 | 1.731,54 |
379 | 2.982,00 | 68,18 |
386 | 1.574,00 | 73,61 |
null | 3.598,00 | null |
415 | 4.212,00 | 57,99 |
466 | 5.729,00 | 1.159,20 |
589 | 2.733,00 | null |
What I need to obtain is the following:
- If CODE=null, then replace nulls with a list containing all the values in CODE except null;
- In VALUE column, replace nulls with GLOBAL VALUE divided by the number of items in that list.
Do you think I need to manage this in PowerQuery or shall I create a set of DAX measures?
Thank You a lot
gianmarco
Solved! Go to Solution.
Is this what you are looking for?
= Table.TransformColumns( #"Changed Type",
{{"CODE", (x)=> if x = null then List.Select(List.Distinct( #"Changed Type"[CODE]), each _<> null) else {x}},
{"GLOBAL VALUE",(x)=> if x = null then List.Select( List.Distinct( #"Changed Type"[GLOBAL VALUE]), each _<> null) else {x}},
{"VALUE",(x)=> if x = null then List.Select( List.Distinct(#"Changed Type"[VALUE]), each _<> null) else {x}}}
)
Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
Dear @Ahmedx, that is close to the solution, thanks!
Once I created the lists in CODE column instead of the null values, how can I expand those lists to rows?
try this
= Table.TransformColumns( #"Changed Type",
{{"CODE", (x)=> if x = null then List.Select( #"Changed Type"[CODE], each _<> null) else {x}},
{"GLOBAL VALUE",(x)=> if x = null then List.Select( #"Changed Type"[GLOBAL VALUE], each _<> null) else {x}},
{"VALUE",(x)=> if x = null then List.Select( #"Changed Type"[VALUE], each _<> null) else {x}}}
)
Dear @Ahmedx , Now I can expand list items in rows. That's fine!
Another question: If a code repeat itself in rows, the list will absorb the duplicates.
What I want is the lists to contain only unique values. I tried List.Distinct instead of List.Select but it won't work... any clues?
what do you mean where these duplicates show
In CODE column a code can show up multiple times but I need the list to show only unique values.
show the expected result for this table
CODE | GLOBAL VALUE | VALUE |
165 | 1.435,00 | 508,00 |
182 | 2.732,00 | 130,60 |
215 | 3.524,00 | 1.456,00 |
null | 2.844,00 | null |
259 | 3.269,00 | 2.889,31 |
343 | 4.589,00 | 590,14 |
null | 1.556,00 | null |
362 | 3.925,00 | 1.731,54 |
379 | 2.982,00 | 68,18 |
386 | 1.574,00 | 73,61 |
null | 3.598,00 | null |
415 | 4.212,00 | 57,99 |
466 | 5.729,00 | 1.159,20 |
589 | 2.733,00 | null |
Dear @Ahmedx, consider instead the following table:
CODE | GLOBAL VALUE | VALUE |
165 | 1.435,00 | 508,00 |
165 | 1.900,00 | 600,00 |
182 | 2.732,00 | 130,60 |
215 | 3.524,00 | 1.456,00 |
215 | 4.000,00 | 200,00 |
null | 2.844,00 | null |
259 | 3.269,00 | 2.889,31 |
343 | 4.589,00 | 590,14 |
null | 1.556,00 | null |
362 | 3.925,00 | 1.731,54 |
379 | 2.982,00 | 68,18 |
386 | 1.574,00 | 73,61 |
null | 3.598,00 | null |
415 | 4.212,00 | 57,99 |
415 | 3.200,00 | 600,00 |
415 | 4.000,00 | 200,00 |
466 | 5.729,00 | 1.159,20 |
589 | 2.733,00 | null |
You can see values 165, 215 and 415 are repeated multiple times in CODE column.
What I need to obtain is where CODE=null, replace null with a list of all the unique CODE values.
Is this what you are looking for?
= Table.TransformColumns( #"Changed Type",
{{"CODE", (x)=> if x = null then List.Select(List.Distinct( #"Changed Type"[CODE]), each _<> null) else {x}},
{"GLOBAL VALUE",(x)=> if x = null then List.Select( List.Distinct( #"Changed Type"[GLOBAL VALUE]), each _<> null) else {x}},
{"VALUE",(x)=> if x = null then List.Select( List.Distinct(#"Changed Type"[VALUE]), each _<> null) else {x}}}
)
That's exactly what I was looking for, thanks a lot!
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
63 | |
62 | |
52 | |
39 | |
24 |
User | Count |
---|---|
84 | |
57 | |
45 | |
43 | |
36 |