Converting from months to days in Power Query for every category

[vpatel55]

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

[latimeria · Accepted solution]

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"

[vpatel55]

@latimeria you're a SUPERSTAR! Thanks so much, that works great. Now to study how it works! Many thanks and much appreciated 😄

[ImkeF]

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

[vpatel55]

Hello @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.