cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Miles1987 Frequent Visitor
Frequent Visitor

Merge tables based on date

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.

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User IV
Super User IV

Re: Merge tables based on date

@Miles1987 ,

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. 

merge table1.PNG

This gives only the latest dates for each emp.

Then merge the the table back into itself.

merge table.PNG

You will see the table with some double headed arrows. Click on them, and you will see this dialogue.merge table3.PNG

Now you should have the table you need to combine with your employee table.

merge table4.PNG

Then merge

merge table5.PNG

And Expand again.

merge table6.PNG

 

and you have it.

merge table7.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Super User IV
Super User IV

Re: Merge tables based on date

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Super User IV
Super User IV

Re: Merge tables based on date

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.





Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Miles1987 Frequent Visitor
Frequent Visitor

Re: Merge tables based on date

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 😊

 

Power Query.jpg

Super User IV
Super User IV

Re: Merge tables based on date

@Miles1987 ,

 

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

lookup 10 17 2019.PNGlookup 10 17 2019 1.PNG

 

       





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Super User IV
Super User IV

Re: Merge tables based on date

@Miles1987 ,

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. 

merge table1.PNG

This gives only the latest dates for each emp.

Then merge the the table back into itself.

merge table.PNG

You will see the table with some double headed arrows. Click on them, and you will see this dialogue.merge table3.PNG

Now you should have the table you need to combine with your employee table.

merge table4.PNG

Then merge

merge table5.PNG

And Expand again.

merge table6.PNG

 

and you have it.

merge table7.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors