Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a report with multiple tables, I have created as summarized table of the Employee Number and Employee Names Column as I realised that two of the tables aren't matching from the data that is feeding through.
One issue I have come across due to the quality of the data is that some of the Employee Numbers and Names are logged as "0". I've given an example of the type of tables that I am looking at (table 1 is an example of issues with the data and table 2 is the summarized table):
Table 1 | |
Employee Number | Employee Name |
1 | John Smith |
1 | John Smith |
1 | John Smith |
0 | John Smith |
5 | Sally Evans |
5 | Sally Evans |
0 | 0 |
0 | Sally Evans |
2 | James Grey |
3 | Tom Tool |
4 | Ryan Poole |
Summarized Table 2 | |
Employee Number | Employee Name |
1 | John Smith |
2 | James Grey |
3 | Tom Tool |
4 | Ryan Poole |
5 | Sally Evans |
I am looking to create two columns one for Employee Number and the other for Employee Name:
I hope this makes sense but let me know if it's not clear.
Solved! Go to Solution.
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
Proud to be a Datanaut!
Try adding speech marks here:
Also, this part doesn't do anything:
I think you want to be looking up the [Employee Number] based on an [Employee NAME] match.
Pete
Proud to be a Datanaut!
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?
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
Proud to be a Datanaut!
Hi Pete,
Thanks for the recommendation although for me to merge these it causes a lot of issues with the rest of the data/tables/calculations already in the dashboard so I'm hoping to resolve it with a DAX query instead
No problem.
In future, you should post DAX queries to either the Desktop forum or the DAX forum. This is the Power Query forum, so you'll get Power Query answers.
Pete
Proud to be a Datanaut!
Ah ok apologies, I'll repost there.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.