Forum Discussion
Solved
inserting records for missing months
Hi Power Query Experets. I have a table with monthly values, i would like to enter 0 dollar records for all month not in the table Can you please help? Data: account year month amount ...
let months=Record.FromList(List.Repeat({0},12), {"1".."9", "10","11","12"}), Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMjIwMgRSIKahgVKsDpq4GUjcFFPcEsTEIm5oBDIMzSCQmDkWC0DiFggLjIxNkQyCqo8FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [account = _t, year = _t, month = _t, amount = _t]), #"Modificato tipo" = Table.TransformColumnTypes(Origine,{{"account", Int64.Type}, {"year", Int64.Type}, {"month", type text}, {"amount", Int64.Type}}), #"Raggruppate righe" = Table.Group(#"Modificato tipo", {"account", "year"}, {"all", each Record.ToTable(months&Record.FromList([amount],[month]))}), #"Tabella all espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "all", {"Name", "Value"}, {"Month", "Amount"}) in #"Tabella all espansa"
First create a calendar table. It can be a month level table, containing all the unique months, including the missing months. https://exceleratorbi.com.au/power-bi-calendar-tables/
You must have a unique id column. I suggest something like 202101 for Jan, 202102 for Feb, etc. this is the primary key
create the same key in your existing table show above - year *100 + month
join the 2 tables. Note, it will create a 1:1 relationship. Change it to a 1:many relationship where calendar filters the table above.
use the year and month column from the calendar table in your final visual on the report
write a measure total amount =sum(table[amount])+0
put the measure in your visual.