Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Gurus,
I have 2 tables in direct query (Table A & Table B). Table is joined with Id, Now I need to compare 2 tables based of date and show that lastest date row as output.
Table A
Id | Name | Sal | Date |
1 | A | 60 | 8.1.2019 |
2 | B | 80 | 8.1.2019 |
3 | C | 50 | 7.1.2019 |
Table B
Id | Name | Sal | Date |
1 | A | 70 | 9.1.2019 |
2 | B | 50 | 7.1.2019 |
3 | C | 70 | 9.1.2019 |
Output : Based on the max date compared between two tables, I need to show the records
Id | Name | Sal | Date |
1 | A | 70 | 9.1.2019 |
2 | B | 80 | 8.1.2019 |
3 | C | 70 | 9.1.2019 |
Thanks in Advance,
Shamar
Solved! Go to Solution.
Hi @Anonymous
You may use 'Merge queries' and then add condition column to get the latest date.Attached the sample file for your reference.
let Source = Table.NestedJoin(TableA,{"Id"},TableB,{"Id"},"TableB",JoinKind.LeftOuter), #"Expanded TableB" = Table.ExpandTableColumn(Source, "TableB", {"Sal", "Date"}, {"TableB.Sal", "TableB.Date"}), #"Added Conditional Column" = Table.AddColumn(#"Expanded TableB", "Custom", each if [Date] > [TableB.Date] then [Date] else [TableB.Date]), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Custom] = [Date] then [Sal] else if [Custom] = [TableB.Date] then [TableB.Sal] else null), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Sal", "Date", "TableB.Sal", "TableB.Date"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}, {"Custom.1", "Sales"}}) in #"Renamed Columns"
Regards,
Cherie
Hi @Anonymous
You may use 'Merge queries' and then add condition column to get the latest date.Attached the sample file for your reference.
let Source = Table.NestedJoin(TableA,{"Id"},TableB,{"Id"},"TableB",JoinKind.LeftOuter), #"Expanded TableB" = Table.ExpandTableColumn(Source, "TableB", {"Sal", "Date"}, {"TableB.Sal", "TableB.Date"}), #"Added Conditional Column" = Table.AddColumn(#"Expanded TableB", "Custom", each if [Date] > [TableB.Date] then [Date] else [TableB.Date]), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Custom] = [Date] then [Sal] else if [Custom] = [TableB.Date] then [TableB.Sal] else null), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column1",{"Sal", "Date", "TableB.Sal", "TableB.Date"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Date"}, {"Custom.1", "Sales"}}) in #"Renamed Columns"
Regards,
Cherie
Hi V-chech-msft,
Thanks for your solution, There is other part in my scenario is
if ( TableA. Date > TableB.Date )
then - > show all columns from TableA
else -> show all columns from Table B
How to achieve this?
Thanks,
Shamar
Hi @Anonymous
I would suggest you create a new thread on forum so that more community members can see it and provide advice. Please remember to post dummy data and desired result.
Regards,
Cherie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |