Forum Discussion

Miles1987's avatar
Miles1987
Frequent Visitor
6 years ago
Solved

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 merg...
Need Help
  • Nathaniel_C's avatar
    Nathaniel_C
    6 years ago

    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. 

    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.

     

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

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

 

       

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

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. 

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.