Table from month to daily

[eduardosilvin3]

Hello all, I need your help, I have a table with values per month and each month is in a different column, with the header as the first day of the month just as reference for that month. What I need is :

-a not manual way to divide that between the total number of days of that month. (that is simple, I know)

-then with the value per day, instead have the columns, create a row per each day and assign the value to each date.

I made some manual examples:

Doing that manually will take a lot because I have 1,000 rows in the current db so the final rows will be like 366,000 rows just for 2024

IMPORTANT= I am sending this from Excel to SQL Server and then to PBI, so I know that here we have a lot of masters in PBI, but if anyone have any idea on any of the 3 I will appreciate it so much

[kpost · Accepted solution]

I attached a .pbix file showing the steps and solving the problem for you in Power Query.

1) unpivot columns to the right of Col3

2) Add Days in Month column

3) Divide value by Days in Month

4) Add "End of Month" 
5) Add List of values between start of month and end of month.

6) Expand the list

//// Mediocre Power BI Advice, but it's free ////

[eduardosilvin3]

Thanks for the response, almost there but still missing each row for each day of the month

I have 1 column for each month (12 months-12 col), I need to convert that into 1 row per day (366 days-1 col). 

[kpost]

I edited my original response to include a new .pbix file with the solution I believe you are looking for.

It fills in each row with every date in the month as a new column, as well as the [value] / [Days in Month] column.

[eduardosilvin3]

THAAANK YOU SO MUCH, worked perfectly.

Have a great day!