Forum Discussion
Consolidated table from different sources
Hi All,
I have condition to consolidate 2 tables from different sources become one table as in picture.
The consolidate table should have latest data so if same UID located must be replace with the newest record from either each table site A or B.
Is it possible to apply the condition in Power BI. Highly appreciate for the answer.
Thanks & Regards,
Supriatna
Hi Anonymous ,
1) Append both tables
2) Sort by date in descending order and use Table.Buffer to keep the sort order ( https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-in-Power/ba-p/810390 )
3) Check column "UID" and remove duplicates.Removing duplicates should keep the topmost row, so with the sorting before this should achieve what you've requested.
But using Table.Buffer is essential, as otherwise the sort order might not be kept.
4 Replies
- amitchandak
Super User
Anonymous , append and group in power Query
https://radacad.com/append-vs-merge-in-power-bi-and-power-query
https://www.poweredsolutions.co/2019/07/30/grouping-rows-with-power-bi-power-query/
union and summarize in DAX
summarize(union(siteA,SiteB),UID, "Field A" , max(Table[Field A]), "Field B" , max(Table[Field B]), "Field C" , max(Table[Field C]), "Updated Date" , max(Table[Updated Date]))
- Greg_Deckler
Community Champion
Anonymous - Hmm, ugly. ImkeF any ideas on this one? I could potentially think of a DAX way of doing this but it wouldn't be particularly pretty.
You could try merging the tables on UID but give the second table's columns names that identify them as the second table. Create new columns that check which date is newer and then populate these new columns with the correct original columns. Then, select out these new columns as the final table.
- ImkeF
Hi Anonymous ,
1) Append both tables
2) Sort by date in descending order and use Table.Buffer to keep the sort order ( https://community.powerbi.com/t5/Community-Blog/Bug-warning-for-Table-Sort-and-removing-duplicates-in-Power/ba-p/810390 )
3) Check column "UID" and remove duplicates.Removing duplicates should keep the topmost row, so with the sorting before this should achieve what you've requested.
But using Table.Buffer is essential, as otherwise the sort order might not be kept.
Hi Anonymous ,
You can follow the below steps to achieve it:
1. Append queries for Site A and Site B table(For example: Site B append to Site A table) in Query Editor
Merge query
2. Create 3 measures
A = CALCULATE(MAX('Site A'[Field A]),FILTER('Site A','Site A'[Update Date]=MAX('Site A'[Update Date])))B = CALCULATE(MAX('Site A'[Field B]),FILTER('Site A','Site A'[Update Date]=MAX('Site A'[Update Date])))C = CALCULATE(MAX('Site A'[Field C]),FILTER('Site A','Site A'[Update Date]=MAX('Site A'[Update Date])))3. Create a table visual and drag UID, these new measures and Update Date onto Table visual
Best Regards
Rena
