Create a column which contains a list of values based on another column

[gianmarco]

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

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RijBEuIB6XELVZNMb?e=AOStoX

[gianmarco]

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?

[Ahmedx]

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}}}
)

[gianmarco]

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?

[Ahmedx]

what do you mean where these duplicates show

[gianmarco]

In CODE column a code can show up multiple times but I need the list to show only unique values.

[Ahmedx]

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

[gianmarco]

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.

[Ahmedx · Accepted 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}}}
)

[gianmarco]

That's exactly what I was looking for, thanks a lot!