topic Re: inserting records for missing months in Power Query

inserting records for missing months

ogend — Sun, 24 Apr 2022 03:15:40 GMT

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!

Re: inserting records for missing months

MattAllington — Sun, 24 Apr 2022 05:18:52 GMT

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.

Re: inserting records for missing months

Vijay_A_Verma — Sun, 24 Apr 2022 06:05:37 GMT

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"

Re: inserting records for missing months

Anonymous — Sun, 24 Apr 2022 15:55:47 GMT

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"

Re: inserting records for missing months

vasu1102 — Fri, 18 Nov 2022 20:40:32 GMT

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?

Pankaj