Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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"
Result
Regards
Phil
Proud to be a Super User!
@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
@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. 🙂
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
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"
Result
Regards
Phil
Proud to be a 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))
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |