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.
Hi all,
I am new to Power Query, so this might be a basic question.
I am looking to merge to tables, one containing employee information, and the other a log employee activity. I want to merge only the latest activity in to my employee information table based one date. But how do I do this?
When I do a normal merge Power Query creates new rows for each activity the employee has had.
Solved! Go to Solution.
Figured out how do it in Power Query.
Do you work in Power BI at all?
If so, here is the PBIX. Merging tables
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Here is first table.
ee date Column1 1 1/1/2019 v 1 1/3/2019 s 1 1/20/2019 p 2 1/10/2019 w 2 1/15/2019 x 3 1/1/2019 a 3 1/4/2019 b
Column1 Column2 1 2 3
Above is second table but you only need Column 1
Then make a duplicate of first table.
Next group by on first table.
This gives only the latest dates for each emp.
Then merge the the table back into itself.
You will see the table with some double headed arrows. Click on them, and you will see this dialogue.
Now you should have the table you need to combine with your employee table.
Then merge
And Expand again.
and you have it.
Proud to be a Super User!
You can try union and summarize
Sheet = union(SUMMARIZE(Sales,Sales[Brand],"Col1",sum(Sales[Sales]),"Col2",sum(Sales[COGS]),"sort",COUNTROWS(Sales)) ,SUMMARIZE(Sales1,Sales1[Brand],"Col1",sum(Sales1[Sales]),"Col2",sum(Sales1[COGS]),"sort",COUNTROWS(Sales1)) )
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
Hi @Miles1987 ,
Would you be able to provide us with a couple of sample tables, and what you would expect. It is easy to dummy up in Excel, and post here. Short as you can make them, but with enough info to show us where you want to go.
Thanks,
Nathaniel
Proud to be a Super User!
Sure
What I am trying to do is that I in Table 1, is to find my employees last activity code from Table 2, based on Activity Date.
When I do a normal merge Power Query creates new rows for each activity the employee has had, but I am only interested in their last one. If that makes sense 😊
This was done in Power BI, although I am sure there is a way to do it in Power Query. Used a column to collect the code from the last date for each client, and then used a column using lookup value to get that to the employee table.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Latest Code = //Add column to Activity table VAR _maxDate = CALCULATE ( MAX ( activity[Activity Date] ), ALLEXCEPT ( activity, activity[Employee ID] ) ) VAR calc = CALCULATE ( MAX ( activity[Activity Code] ), ALLEXCEPT ( activity, activity[Employee ID] ), activity[Activity Date] = _maxDate ) RETURN calc
Lookup = LOOKUPVALUE(activity[Latest Code],activity[Employee ID],Emp[Employee ID]) //add to emp table
Proud to be a Super User!
Figured out how do it in Power Query.
Do you work in Power BI at all?
If so, here is the PBIX. Merging tables
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
Here is first table.
ee date Column1 1 1/1/2019 v 1 1/3/2019 s 1 1/20/2019 p 2 1/10/2019 w 2 1/15/2019 x 3 1/1/2019 a 3 1/4/2019 b
Column1 Column2 1 2 3
Above is second table but you only need Column 1
Then make a duplicate of first table.
Next group by on first table.
This gives only the latest dates for each emp.
Then merge the the table back into itself.
You will see the table with some double headed arrows. Click on them, and you will see this dialogue.
Now you should have the table you need to combine with your employee table.
Then merge
And Expand again.
and you have it.
Proud to be a Super User!
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.