How does Power Query updates data?

[IPGeorgiev]

Hi all,

I have the following question I will try to explain with a simple example :

1. Data source is Microsoft Exchange - looking for an emails with a specific subject and taking the attachment as data.

On daily basis I receive a report for the last 14 days so this report has a lot of columns, one of them is Date and the other one is Case ID.

So basically everyday the new report has overlapping with the report from the previous day for 13 out of 14 days data. However although the column case id and data will remain the same the other columns will adjust their values over time.

So if on the 2nd of February the value of a Case ID called JJJ - for column called length was 10 in the report on the 3rd of February we will have the same Case ID called JJJ but the value for column length would be 12.

So I want to always have the newest value for a given case id for all other columns.
Is this possible in Power BI?

Many thanks in advance!!

[Anonymous]

Hi @IPGeorgiev ,

Despite the validity of what @MattAllington suggested for detaching and storing reports using MS Flow, I guess mentioning MS Exchange in your post to me looks a bit remote to the problem you have. This relates to the file/data that you receive, not to sourcing it from Exchange server, right?

In the file, the problem is not that some data change over time, as this is surely controlled from somewhere else and not impacted by PBI, You need somehow update these "new" data against some global table/storage so that it would contain the most recent version of the data in the columns that have changed? Kind of incremental data update?

If my understanding correct, you can use Table.NestedJoin (i.e.. merge tables) to get the set of data for Date / CaseId combinations in the old/stored data that also present in the new/14 day data in the file. Remove the identified data from the main table/storage and then append new data from the file.

Hope this helps, otherwise, you may need to further explain your case :).

Kind regards,

JB

[IPGeorgiev · Accepted solution]

Hi,

many thanks for the response!

I have actually tried another approach  - where I have selected the columns which will always be the same ( I mean in the new raw data their value wont change) and having all those selected columns Right click and Remove duplicated - this seems to be doing the job just fine for now 🙂

Best regards,

Ivan 

[Anonymous]

Thank you Ivan

Do you mind marking your post as a solution to close the topic?

Thanks.
John

[MattAllington]

Have you considered Power Flow as the tool here?  You can get flow to wait for the email, detach the file and then trigger the refresh of Power BI