Forum Discussion
Repeat the same value for whole month
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.
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"
2 Replies
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"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.
