Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gianmarco
Helper IV
Helper IV

Reference a a column for a list in Power Query

Dear All,

I have the following table:


Table1

CODEVALUE
7241.000

323

500

null

200
455300
null1.200
444600
256700

 

Through the following step in PowerQuery:

= Table.TransformColumns( #"previous step", {{"CODE", (x)=> if x = null then List.Select(List.Distinct( #"previous step"[CODE]), each _<> null) else {x}}})

I achieve the following:

in column CODE, replace "null" with a list of all the distinct codes existent in column CODE except "null".

In this way, instead of nulls, I get a list of the following elements {724, 323, 455, 444, 256} 

 

 

Now, I have another table with a list of codes:

 

Table2

CODEVALUE
123100

829

100
047150
299300

 

My question is: How do I reference CODE column in Table 2 in the previous step INSTEAD of CODE column in Table1?


Thank you for your time

gianmarco

2 ACCEPTED SOLUTIONS

Dear @Ahmedx ,

it seems that your formula si replacing not only the "nulls" in CODE column but all the elements instead.

I think I need to change this formula which i'm actually using:

= Table.TransformColumns( #"previous step", {{"CODE", (x)=> if x = null then List.Select(List.Distinct( #"previous step"[CODE]), each _<> null) else {x}}})

 

This formula is actually replacing the nulls in code columns with a list of all the CODEs in Code column except null in TABLE1.

View solution in original post

7 REPLIES 7
Ahmedx
Super User
Super User
Ahmedx
Super User
Super User

write the expected result, it’s not clear what you want

Dear @Ahmedx ,

consider I have the following tables:

 

Table1

CODEVALUE
7241.000

323

500

null

200
455300
null1.200
444600
256700

 

Table2

CODEVALUE
123100

829

100
047150
299300

 

My expected result is:

replace nulls in table 1 with a list containing the elements of table 2. In this case: {123,829,047,299}

 

 

Dear @Ahmedx ,

it seems that your formula si replacing not only the "nulls" in CODE column but all the elements instead.

I think I need to change this formula which i'm actually using:

= Table.TransformColumns( #"previous step", {{"CODE", (x)=> if x = null then List.Select(List.Distinct( #"previous step"[CODE]), each _<> null) else {x}}})

 

This formula is actually replacing the nulls in code columns with a list of all the CODEs in Code column except null in TABLE1.

it is possible, but my code essentially it only changes null

Ah, you're right!

Gonna give it a try, then mark your answer as solution 🙂

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.