Forum Discussion
Multiple column and Table Query Dependant on values
Hi YellowSquirrel ,
Assuming that your Table2 isn't referenced from Table1, then the simplest and clearest way would probably be to merge Table2 onto Table1 on Table1[EmployeeName] = Table2[EmployeeName] and expand Table2[EmployeeNumber].
Then do the same, but on Table1[EmployeeNumber] = Table2[EmployeeNumber] and expand Table2[EmployeeName].
You can now create two new custom columns, something like this:
employeeNumberClean = if [Employee Number] = 0 then [Table2.Employee Number] else [Employee Number] employeeNameClean = if [Employee Name] = "0" then [Table2.Employee Name] else [Employee Name]I've attached a PBIX below with the query to change this:
...to this:
Pete
Ah ok apologies, I'll repost there.
Hi YellowSquirrel ,
Please try:
Employee Number 2.
if [Employee Number] = 0 and [Employee Name] = "0"
then 0
else if [Employee Number] = 0
then Table.SelectRows(Table2,(x)=>x[Employee Name]=[Employee Name])[Employee Number]{0}
else [Employee Number]Employee Name 2.
if [Employee Number] = 0 and [Employee Name] = "0"
then 0
else if [Employee Name] = "0"
then Table.SelectRows(Table2,(x)=>x[Employee Number]=[Employee Number])[Employee Name]{0}
else [Employee Name]
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi, I tried to recreated your calculation but I'm not sure what some of it means. When I download your pbix file it says you're on a newer version of power BI than myself. I've looked at it and it just pulls through the columns as columns so I can't see how you've calculated it, maybe it's due to the difference power bis?
Can I ask what do you mean by as I wasn't sure
(x)=>x
and I can't quite do the calculation as you've written it with the AND so I've started it as: If(Table 1[Employee Name] = 0 && Table 1[Employee Number]=0, 0
Does this look correct?