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"
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)
let
Source = 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]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"account", Int64.Type}, {"year", Int64.Type}, {"month", Int64.Type}, {"amount", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"month", "amount"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
#"Added Index" = Table.AddIndexColumn(#"Removed Duplicates", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "month", each {1..12}),
#"Expanded month" = Table.ExpandListColumn(#"Added Custom", "month"),
#"Merged Queries" = Table.NestedJoin(#"Expanded month", {"account", "year", "month"}, #"Changed Type", {"account", "year", "month"}, "Expanded month", JoinKind.LeftOuter),
#"Expanded Expanded month" = Table.ExpandTableColumn(#"Merged Queries", "Expanded month", {"amount"}, {"amount"}),
#"Sorted Rows" = Table.Sort(#"Expanded Expanded month",{{"Index", Order.Ascending}, {"year", Order.Ascending}, {"month", Order.Ascending}}),
#"Replaced Value" = Table.ReplaceValue(#"Sorted Rows",null,0,Replacer.ReplaceValue,{"amount"}),
#"Removed Columns1" = Table.RemoveColumns(#"Replaced Value",{"Index"})
in
#"Removed Columns1"