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

Merge tables based end date of another table

hi,

I have 2 tables with overlap dates.

Table A is actual cost with dates.

Table B is predicted cost with dates.

 

I want to merge Table B into Table A where the Date in Table B start when max date in Table A.

 

eg: Table A data is from 01/01/2020 to 01/12/2020, Table B data is from 01/01/2020 to 01/01/2022.

The merge table would be data from Table A between 01/01/2020 to 01/12/2020 and Table B from 02/12/2020 to 01/01/2022.

 

Thanks in advance!

2 ACCEPTED SOLUTIONS
PhilipTreacy
Super User
Super User

Hi @ChimDen 

Download example PBI file here with these queries and data.

You can do an intermediate merge to get just the rows from the 2nd table then merge that result with the first table.

let
    Source = Table.NestedJoin(Actual, {"Date"}, Merge1, {"Date"}, "Merge1", JoinKind.FullOuter),
    #"Expanded Merge1" = Table.ExpandTableColumn(Source, "Merge1", {"Predicted", "Date"}, {"Predicted", "Date.1"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Merge1", "Custom", each if [Date] <> null then [Date] else [Date.1]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Actual] <> null then [Actual] else [Predicted]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Actual", "Date", "Predicted", "Date.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}, {"Custom.1", "Value"}})
in
    #"Renamed Columns"

actual.png

predicted.png

 

Result

result.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

ChimDen
Frequent Visitor

@PhilipTreacy @AllisonKennedy  thanks guys, i have combined your ideas and modify Phil's soultion slightly as per attached PBIX file by making use of Append query (after renamed both Predicted and Actual columns to "Value") for results rather than Merge query.

 

let
    Source = Table.Combine({Actual, Merge1})
in
    Source

 

 

View solution in original post

4 REPLIES 4
ChimDen
Frequent Visitor

@PhilipTreacy @AllisonKennedy  thanks guys, i have combined your ideas and modify Phil's soultion slightly as per attached PBIX file by making use of Append query (after renamed both Predicted and Actual columns to "Value") for results rather than Merge query.

 

let
    Source = Table.Combine({Actual, Merge1})
in
    Source

 

 

View solution in original post

Thanks for the update @ChimDen 

I think append should be more efficient than merge, so good choice and well done combining two suggestions. I have marked your post as solution in addition, since a single post can have as many solutions as required, as this will make it easier for people to know what you have done. 🙂 


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

PhilipTreacy
Super User
Super User

Hi @ChimDen 

Download example PBI file here with these queries and data.

You can do an intermediate merge to get just the rows from the 2nd table then merge that result with the first table.

let
    Source = Table.NestedJoin(Actual, {"Date"}, Merge1, {"Date"}, "Merge1", JoinKind.FullOuter),
    #"Expanded Merge1" = Table.ExpandTableColumn(Source, "Merge1", {"Predicted", "Date"}, {"Predicted", "Date.1"}),
    #"Added Conditional Column" = Table.AddColumn(#"Expanded Merge1", "Custom", each if [Date] <> null then [Date] else [Date.1]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Actual] <> null then [Actual] else [Predicted]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Actual", "Date", "Predicted", "Date.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}, {"Custom.1", "Value"}})
in
    #"Renamed Columns"

actual.png

predicted.png

 

Result

result.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

AllisonKennedy
Super User
Super User

@ChimDen  You are looking for an append or union (not a merge technically speaking). so you can either do this in Power Query by filtering the predicted table for on or after today (you can do relative date filters in Power Query) and then append the tables.

Or, you can do this in DAX using the UNION function. You'll need to find the MAX date first and 

 

Table =

VAR maxDate = MAX(TableA[Date])

UNION( TableA, FILTER(TableB, TableB[Date] > maxDate))


Please @mention me in your reply if you want a response.

Check out my Tokyo 2020 report with live stats - KUDOS much appreciated

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!