Power Query Help - Create Daily data from Weekly source data

Matt888

New Member
Joined
Dec 16, 2021
Messages
7
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I'm very new to using Power BI. Hoping to get some advice on the below...

I have 2 sources of data is broadly the same format. I have imported these to Power BI and merge the tables into one (AllWeeklySales). The table has 6 attribute columns and 2 metric columns.

The sales data is only at calendar week level and I have a time dimension table built up from calendar date (Year-Week Number e.g. 202001 is the matching field). Is there a way in Power Query to:-

  • Disaggregate the weekly level data daily level including the all sales data table and the date dimension table? (i.e. the metrics will be 1/7 of the weekly value unless the calendar week is a part week over the year end, in which case it will be the weekly value divided by the days in that “week”)
  • If this is possible can the resulting daily sales data table include all the fields from the weekly sales table and only calendar date from the date table?
  • If the daily table is create using Power Query, once in the model creating visualisations am I able to create new measures using DAX based on the metrics in the daily sales data table. (e.g. I might want to calculate weight based on the number of items and their individual weight defined in a related table)?
Thanks
Matt
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Stating the obvious maybe, but can't you get your input data fixed, so you have daily sales?
Else I advice to do weekly analysis over daily. As your sales can't be weeksales/number of days in the week. You can still use the calendar table, as the reference date might be any of the dates of that week (most common option would be to use first day of week or last day of week).
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top