Reply
kulkarnipriya87
Helper IV
Helper IV
Partially syndicated - Outbound

Convert data from monthly format into weekly

Screenshot (353).png



I have my data in a monthly format. But I want to convert it into a weekly format.
My column data type is text currently. And I'm unable to convert it into date data type.

Expected Output : 

Sr.No.    Customer Code     Jan23(Week1)   Jan23(Week2)   Jan23(Week3)  Jan23(Week4)  Jan23(Week5) 

Every month should divide into weeks.
I don't have a date column.

Can anyone help?

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

Syndicated - Outbound

1) in power query divided sales into daily sales and created a date in the same place
2) I created a calendar, if you don't like this calendar you can create your own

https://1drv.ms/u/s!AiUZ0Ws7G26RhxIEmMaicVrgJuM3?e=8hbv7L

View solution in original post

23 REPLIES 23
Ahmedx
Super User
Super User

Syndicated - Outbound

1) in power query divided sales into daily sales and created a date in the same place
2) I created a calendar, if you don't like this calendar you can create your own

https://1drv.ms/u/s!AiUZ0Ws7G26RhxIEmMaicVrgJuM3?e=8hbv7L

Syndicated - Outbound

Great. You did fantastic work. But I have got an error.

"Column 'Value' in the table cannot be found or may not be used in this expression. Column 'Value' in the table cannot be found or may not be used in this expression. Column 'Value' in the table  cannot be found or may not be used in this expression."

Syndicated - Outbound

in this version I made a small change, removed the weekend days
https://1drv.ms/u/s!AiUZ0Ws7G26RhxQzx1qDV_e2UulW?e=Dj5M3M

Syndicated - Outbound

In the Second Calender. also some totals are wrong.
It differs by 1.
I'm attaching an image for your reference.
Thanks. WhatsApp Image 2023-04-11 at 3.37.02 PM.jpeg

Syndicated - Outbound

only need to round to two decimal places
Screen Capture #856.png

Syndicated - Outbound

https://www.dropbox.com/scl/fi/g2vcupkp6ghzykfubcj1p/Merged-Forecast.xlsx?dl=0&rlkey=p35g27v9nxmcsqq...


Please check this data.
There are 3 sheets.
In first, Data is in weekly basis.
In second and third, Data is in monthly basis.

Now I want to append all these sheets and final result should displayed in weekly basis for all of these.

Please help.

And also tell me if this conversion and Append possible in Excel.

Syndicated - Outbound

Done.Thank you so much for your help.

Syndicated - Outbound

look again carefully what and how I did, I don’t know what you have
the "Value" column is the sum column

Syndicated - Outbound

Failed to save modifications to the server. Error returned: 'Cannot convert value 'May'23' of type Text to type Date.
The current operation was cancelled because another operation in the transaction failed.
'.

 

Syndicated - Outbound

give the file to see what is causing this error

kulkarnipriya87
Helper IV
Helper IV

Syndicated - Outbound
Sr.NoCodeJan23Feb23March23April23May23Total
1575200100506080490
21254030467090286

I have data in this monthly format.I want to convert it into weekly format without a date column.

Syndicated - Outbound

Hi,

Remove the Total column.  Select the first 2 columns, right click and click on "Unpivot Other Columns".  Rename the Attribute column to Dae and ensure that this column has the Data Type of Date.  Create a Calendar Table with calculated column formulas for Year, Month name, Month number and Week number.  Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  To your visual, drag the Month name and Week number column from the Calendar Table.

Hope this helps. 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Syndicated - Outbound

Thank yo so much. It works. But now how to divide that value of a month into weeks equally?

Syndicated - Outbound

Hi,

I can try to help if you share the download link of the PBI file.  In that PBI file, ensure that there is a Calendar Date with a week number column.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Syndicated - Outbound

Hi,

Also, share the PBI file with a calendar table and week number column.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Syndicated - Outbound

I don't have Date column in data.

 

Syndicated - Outbound

In the Table that you have shared, the column headings are Month and year, When you Unpivot, the Attribute column's data type can be conveted into Date.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)