Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Data conversion monthly to weekly

 

Hello Everyone,

I have 3 diff tables in Power BI.

In Table A, I have weekly data. Weeks are as columns.

In Table B & C, I have monthly data. Months are as columns.

I don't have date column.

Created a custom date column in Table B & C using "Date.AddWeeks(#date(2023,1,1), [WeekNo]-1)" this formula in power query.

I have converted Table B and C into a weekly basis. And merged that 3 tables.

 

Now there is no problem with table A.

But in other tables, Values are assigning to the first week of every month.But I want to assign that monthly value into weekly value to every week of month equally.

 

Can Anyone Help?

Need Help

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    3 years ago

    Hello Anonymous,

     

    One approach to do this would be to first expand the monthly data in Tables B and C into a daily basis using Power Query. You can do this by creating a list of dates for each month, and then merging the monthly data with the list of dates to create a daily table.

    = Table.AddColumn(Table.FromColumns({List.Dates(#date(2023, 1, 1), 12, #duration(31, 0, 0, 0)), {"Month"}}, {"Date", "Month"}), "MonthStart", each Date.StartOfMonth([Date]))

     

    • Sahir_Maharaj's avatar
      Sahir_Maharaj
      Super User
      3 years ago

      2. In Table B, create a custom column with the month name:

       

      = Date.ToText(Date.FromText([Month], "MMM"), "MMMM")

       

      Merge Table B with the daily table created earlier, using the MonthStart column as the key. Then, expand the merged table to include the monthly data for each day in each month.

       

      3. Create a custom column with the weekly date:

       

      = Date.AddDays(Date.StartOfWeek([Date], Day.Monday), 6)

       

      4. Group the data by the weekly date and calculate the sum of the monthly data for each week.

      5. Merge the weekly data in Table A with the sum of the monthly data using the weekly date as the key.

       

      This should assign the monthly values in Tables B and C to the corresponding weeks in Table A. If there are missing weeks or days, you can use the Fill Down feature in Power Query to fill in the missing values.

       

      I hope this helps! Let me know if you have any further questions.