Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Dear All,
I have the following table:
Table1
CODE | VALUE |
724 | 1.000 |
323 | 500 |
null | 200 |
455 | 300 |
null | 1.200 |
444 | 600 |
256 | 700 |
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
CODE | VALUE |
123 | 100 |
829 | 100 |
047 | 150 |
299 | 300 |
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
Solved! Go to Solution.
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.
write the expected result, it’s not clear what you want
Dear @Ahmedx ,
consider I have the following tables:
Table1
CODE | VALUE |
724 | 1.000 |
323 | 500 |
null | 200 |
455 | 300 |
null | 1.200 |
444 | 600 |
256 | 700 |
Table2
CODE | VALUE |
123 | 100 |
829 | 100 |
047 | 150 |
299 | 300 |
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 🙂
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
71 | |
56 | |
39 | |
35 |
User | Count |
---|---|
66 | |
66 | |
59 | |
53 | |
45 |