Forum Discussion
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 |
| 123 | 2021 | 3 | 10 |
| 123 | 2021 | 6 | 15 |
| 123 | 2021 | 9 | 25 |
| 123 | 2021 | 12 | 30 |
| 123 | 2022 | 7 | 10 |
| 123 | 2022 | 8 | 15 |
| 235 | 2021 | 1 | 10 |
needed output:
| account | year | month | amount |
| 123 | 2021 | 1 | 0 |
| 123 | 2021 | 2 | 0 |
| 123 | 2021 | 3 | 10 |
| 123 | 2021 | 4 | 0 |
| 123 | 2021 | 5 | 0 |
| 123 | 2021 | 6 | 15 |
| 123 | 2021 | 7 | 0 |
| 123 | 2021 | 8 | 0 |
| 123 | 2021 | 9 | 25 |
| 123 | 2021 | 10 | 0 |
| 123 | 2021 | 11 | 0 |
| 123 | 2021 | 12 | 30 |
| 123 | 2022 | 1 | 0 |
| 123 | 2022 | 2 | 0 |
| 123 | 2022 | 3 | 0 |
| 123 | 2022 | 4 | 0 |
| 123 | 2022 | 5 | 0 |
| 123 | 2022 | 6 | 0 |
| 123 | 2022 | 7 | 10 |
| 123 | 2022 | 8 | 15 |
| 123 | 2022 | 9 | 0 |
| 123 | 2022 | 10 | 0 |
| 123 | 2022 | 11 | 0 |
| 123 | 2022 | 12 | 0 |
| 235 | 2021 | 1 | 10 |
| 235 | 2021 | 2 | 0 |
| 235 | 2021 | 3 | 0 |
| 235 | 2021 | 4 | 0 |
| 235 | 2021 | 5 | 0 |
| 235 | 2021 | 6 | 0 |
| 235 | 2021 | 7 | 0 |
| 235 | 2021 | 8 | 0 |
| 235 | 2021 | 9 | 0 |
| 235 | 2021 | 10 | 0 |
| 235 | 2021 | 11 | 0 |
| 235 | 2021 | 12 | 0 |
Thank you!
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"
4 Replies
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"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
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 reportwrite a measure total amount =sum(table[amount])+0
put the measure in your visual.
