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"
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"
Hi, This is quite a nice and clean solution. I need a variant of this code where I can have 2 columns (eg. amount and weight) where I want to insert 0's for months not in the table. Does anyone know how that can be done?
BR
Pankaj
