Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 @Anonymous 

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

Anonymous
Not applicable

@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
Anonymous
Not applicable

@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

 

 

Thanks for the update @Anonymous 

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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

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 @Anonymous 

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!


AllisonKennedy
Super User
Super User

@Anonymous  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.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.