Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
eduardosilvin3
Advocate I
Advocate I

Table from month to daily

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:

eduardosilvin3_0-1713475414776.png

 

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

1 ACCEPTED SOLUTION
kpost
Super User
Super User

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 ////

View solution in original post

4 REPLIES 4
kpost
Super User
Super User

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 ////

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). 

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.

THAAANK YOU SO MUCH, worked perfectly.

Have a great day!

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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