Consolidated table from different sources

[Anonymous]

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

[v-yiruan-msft]

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

[Greg_Deckler]

@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 · Accepted solution]

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.

[amitchandak]

@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]))