Expand Date column from showing first of each month to every date in month

[crobson29]

Hello,

I have a column that has a date column that contains the first date of each month.  I want to expand this column to show every date for every month, and duplicate all the rows for each day.  I have a Budget that is formated like this: "Month", "Budget per day" and I want to be able to create a chart showing the cumulative total budget for the month.

[amitchandak · Accepted solution]

@crobson29 , Create Date Table in Power query using List.Dates and also add column using Date.StartOfMonth and then merge your table using StartofMonth and FirstDate in your table

Power BI - Get Dates between range using Power Query List.Dates- https://youtu.be/SFEPsKfqIks

https://medium.com/@amitchandak/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1

Merge Tables (Power Query) : https://youtu.be/zNrmbagO0Oo

Or do it using just date table and Time Intellignece

Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an MS Excel file.  Show the expected result.

[v-xinruzhu-msft · Accepted solution]

Hi @crobson29 

The solution @amitchandak  provided is right, and i want to offer some more information for you to refer to.

You can create a blank query in power query and input the following code to advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtE31DdU0lEyNFCK1YGKGIFFjJBEjMEixkCRWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t, #"Budget per day" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Budget per day", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates([Month],Date.DaysInMonth([Month]),#duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@crobson29 , Create Date Table in Power query using List.Dates and also add column using Date.StartOfMonth and then merge your table using StartofMonth and FirstDate in your table

Power BI - Get Dates between range using Power Query List.Dates- https://youtu.be/SFEPsKfqIks

https://medium.com/@amitchandak/cheat-sheet-power-query-financial-year-calendar-5ceaacb520f1

Merge Tables (Power Query) : https://youtu.be/zNrmbagO0Oo

Or do it using just date table and Time Intellignece

Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...