Add weekly data

[mb0307]

Hi,

Please download 3 sample excels file from here:

FILE DOWNLOAD 

Week1 file is the main file and I want to merge (not aggregate) all the records from other weeks (week2 and 3) into Week1.

As you can imagine data will get bigger by each week so I just want incremental refresh for new file without updating existing data every time.

Can you provide instructions in steps please? 

Thanks

[v-alq-msft]

Hi, @mb0307 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Week1:

Week2:

Week3:

Sales:

Please make sure 'Week1', 'Week2', 'Week3' are in the same folder. You may use folder connector to get the data in each file which has same columns and each file in the a sheet which has the same name.

When you get the data from data source, it displays as below.

Finally you need to add a new step for 'Sales'. You may go to 'Query Editor'=>'Add Columns'=>'Custom Column', input codes as below.

let 
weeknum = [WeekNum], 
tab = Table.SelectRows(week,each [WeekNum]=weeknum),
sum = List.Sum(Table.Column(tab,"Sales"))
in 
sum

Result:

For further information, you may refer to the document .

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mb0307]

@v-alq-msft   Thanks for your response.  Sorry this is not what I am after.

Please download 3 sample excels file from here:

FILE DOWNLOAD 

Week1 file is the main file and I want to merge (not aggregate) all the records from other weeks (week2 and 3) into Week1.

As you can imagine data will get bigger by each week so I just want incremental refresh for new file without updating existing data every time.

Can you provide instructions in steps as you did in your reply please? 

Thanks in advance.

[v-alq-msft]

Hi, @mb0307 

Incremental refresh is now available to Power BI Pro, Premium, and shared subscriptions and datasets. You need to set RangeStart and RangeEnd parameters and use them to filer the data in 'Query Editor'. Then you may define the incremental refresh policy for specific table. For further information, you may refer to the document .

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@mb0307 , I have discussed here how to convert week to date

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3

For data merge

https://medium.com/@wkrzywiec/getting-data-from-the-latest-file-in-a-folder-using-power-query-51dfa4...

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...

https://radacad.com/append-vs-merge-in-power-bi-and-power-query