Building on a dataset with modified data

Power Query
1

Hi

Not sure if this is the correct category to ask this query but here goes.

I have a BI report that analyses 3.5 million rows of sales data. To extract the sales data from our database I have to run about 8 exports and then combine them using Power Query append queries. (The database export query hangs if I try to export all sales records.)

Our database does have a report that outputs only modified or new sales records. If I run this report daily, modified data would only be a few thousand records. A data file this small I can import into Power Query via a get API.

Is it possible to have the sales table sitting somewhere and when the new/modified sales records are loaded daily, data in the sales table is either replaced if the Sale ID already exists or added to the data table if its a new sale?

Each sales record has a unique Sale ID.

Thanks

1 Like
2

Hey,
If you are able to pull data from database to power query , you can try for incremental refresh.
Not sure about API.

3

Hi Pankaj

I don’t think I have the ability to incrementally refresh.

I can however each day run a report that shows the sales records modified yesterday:

image

My thinking was if I had a static table, each day it could be updated with the daily modified data report.

The logic would be if the Sale ID does not exist, add the sale to the static table. If the Sale ID does exist, overwrite the sale with the modified data.

Is this even possible using Power Query?

Thanks

4

Hi @KieftyKids - Could be possible using Merge in Power Query and using Full outer join but again this will need to load all data in power query and perform the join which will be highly memory sensitive and time consuming and may fail also.

Will suggest to consider some other tools like Azure Data Factory (ADF) or Informatica that are made for these purpose.

Thanks
Ankit J

5

Hello @KieftyKids

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

6

Hello @KieftyKids

Just following up if the response above help you solve your inquiry.
If it did, please mark his answer as the SOLUTION.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

7

Hi @KieftyKids

Due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.