Forum Discussion
Kudo CommanderConverting from months to days in Power Query for every category
We have a monthly budget which I'm looking to convert to a daily value budget. This is by dividing the monthly budget by the days in the month. It needs to be done in Power Query (not DAX).
However I need this for every category, and for the model to repeat this, should a new category be added.
Kudos and huge thanks available to anyone who can help. Here is a file with the sample data.
https://www.dropbox.com/s/q7x4r76x1mculp8/Sample.pbix?dl=0
Here is the input:
| Month | Fruit | Count |
| 01/April/2022 | Apples | 819 |
| 01/May/2022 | Apples | 601 |
| 01/June/2022 | Apples | 423 |
| 01/April/2022 | Pears | 819 |
| 01/May/2022 | Pears | 601 |
| 01/June/2022 | Pears | 423 |
| 01/April/2022 | Oranges | 819 |
| 01/May/2022 | Oranges | 601 |
| 01/June/2022 | Oranges | 423 |
This is the expected output for Apples, but the same output would include Pears and Oranges, and any other category that may be added later on. Its dividing the monthly count for that category by the number of days in the month:
| Date | Fruit | Count |
| 01/April/2022 | Apples | 27.3 |
| 02/April/2022 | Apples | 27.3 |
| 03/April/2022 | Apples | 27.3 |
| 04/April/2022 | Apples | 27.3 |
| 05/April/2022 | Apples | 27.3 |
| … | ||
| 01/May/2022 | Apples | 19.3871 |
| 02/May/2022 | Apples | 19.3871 |
| 03/May/2022 | Apples | 19.3871 |
| 04/May/2022 | Apples | 19.3871 |
| 05/May/2022 | Apples | 19.3871 |
| … | ||
| 01/June/2022 | Apples | 14.1 |
| 02/June/2022 | Apples | 14.1 |
| 03/June/2022 | Apples | 14.1 |
| 04/June/2022 | Apples | 14.1 |
| 05/June/2022 | Apples | 14.1 |
Hi vpatel55 ,
Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDRNzIwMlLSUXIsKMhJLQYyLAwtlWJ1ILKmGLJmBoZwWTMMWRMjY7gs3OSA1MQinAbDJLGaC5PEaqx/UWJeOm4XI6SxGo2QBhseCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Fruit = _t, Count = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Fruit", type text}, {"Count", Int64.Type}}), #"Added DailyCount" = Table.AddColumn(#"Changed Type", "DailyCount", each [Count] / Date.DaysInMonth([Month])), #"Added DaysMonth" = Table.AddColumn(#"Added DailyCount", "DaysMonth", each List.Dates([Month], Date.DaysInMonth([Month]), #duration(1,0,0,0))), #"Expanded DaysMonth" = Table.ExpandListColumn(#"Added DaysMonth", "DaysMonth") in #"Expanded DaysMonth"
4 Replies
latimeriaSolution Specialist
Hi vpatel55 ,
Try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDRNzIwMlLSUXIsKMhJLQYyLAwtlWJ1ILKmGLJmBoZwWTMMWRMjY7gs3OSA1MQinAbDJLGaC5PEaqx/UWJeOm4XI6SxGo2QBhseCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, Fruit = _t, Count = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Fruit", type text}, {"Count", Int64.Type}}), #"Added DailyCount" = Table.AddColumn(#"Changed Type", "DailyCount", each [Count] / Date.DaysInMonth([Month])), #"Added DaysMonth" = Table.AddColumn(#"Added DailyCount", "DaysMonth", each List.Dates([Month], Date.DaysInMonth([Month]), #duration(1,0,0,0))), #"Expanded DaysMonth" = Table.ExpandListColumn(#"Added DaysMonth", "DaysMonth") in #"Expanded DaysMonth"- ImkeF
Community Champion
Hi vpatel55 ,
I have a function that generates the dates between a start- and enddate: Date.DatesBetween to retrieve dates between 2 dates in Power BI and Power Query – The BIccountant
As for the enddate-argument, you can use Date.EndOfMonth([Date]).
For allocation purposes, you can create a column Duration.Days([EndDate)-[Date]))
vpatel55Hello ImkeF thanks so much for the response. I'm afraid my knowledge of Power Query is weak (I tried for hours before posting on here). Your fucntion looks useful but probably quite difficult for me to implement. Please would you be able to point me in the right direction on the sample file? Thank you.
