Repeat the same value for whole month

[ahmed_hm]

Hi there, 

I have a table containing the first date of each month and the budget for that day, this VALUE will be fixed daily, now I need to create a new table in Power BI to repeat this value for the whole month, any idea on how to achieve that? 

 appreciate your cooperation.

[v-eqin-msft]

Hi @ahmed_hm ,

You may follow @Vijay_A_Verma 's method which is efficient.

My workaround is firstly getting the EndDate of each month and then converting dates to number type and use {fFirstDate..EndDate} to get full dates, You could create a new blank query and then paste the following M in Advanced Editor dialog:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7JCQAxDEPRXnwOxHb2WoL7b2OUIaBcBE+nv7e4umeTJCaRLh10soCFrGAlG9jIDnZygIOc4CQXuEjT06HP8Yc9ZXbSMBEf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Date" = _t, Budget = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"First Date", type date}, {"Budget", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Full Date", each { Number.From([First Date])..Number.From( Date.EndOfMonth([First Date]))}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Full Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Full Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"First Date"})
in
    #"Removed Columns"

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

[Vijay_A_Verma · Accepted solution]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

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