Forum Discussion
Add weekly data
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.
v-alq-msft Thanks for your response. Sorry this is not what I am after.
Please download 3 sample excels file from here:
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.
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.
