Forum Discussion
Convert data from monthly format into weekly
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) 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
23 Replies
Hi Anonymous ,
What's your expected result? Do you want to divide the month column into multiple (4-5) weekly columns? For example, for the column [Jan-'23], do you want to get [week1],[week2],[week3],[week4] and [week5]? And how to assign the values to these weekly column? Could you please provide some sample data with Text format and the expected result with the specific examples? It would be helpful to find out the solution. You can refer the following links to share the required info:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
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 ownGreat. 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."in this version I made a small change, removed the weekend days
https://1drv.ms/u/s!AiUZ0Ws7G26RhxQzx1qDV_e2UulW?e=Dj5M3M
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.
