Add missing months of year per category (material number) in case the month of year is not present

[stapelbr]

Dear PowerBi Community,

I have tried for hours and hours and read to multiple threads but did not manage to solve this matter. What I would like is to have for each category (material number) at least one row with the month of year which is present in a calendar table (which is not in the query editor). This table is based upon the min and the max date present in the table with the contents.

Calendar table

Content table

So if a certain UIN has only several hits, e.g. only in januari 2024 and/or or november 2023 I would like to have additional rows for the months which are not present in the table. Hence for each unique material I would like to have always all months present like this:

Your expertise is really appreciated to get the query complete with missing months per category!

[serpiva64]

Hi,

i think you can o this way:

- first get your interval dates and transform it to a list

- then duplicate your principal table

- in the duplicated one maintain only a unique column of the material, add a column with the lists of dates, extract them to rows and finally merge this one with the original

You will get from this 

this that i think is your goal

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

Here you can copy the example

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUpPzdM1MgYygESsTrRSEpCZn1wCFTMBi4HUFafCxEzBYslAZm5iEUJvLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column2", type date}, {"Column3", Int64.Type}})
in
#"Changed Type"

[stapelbr]

Thanks Serpiva, spot on! The only issue I face now is that the new table created keeps waiting for the source to apply the changes made. It is waiting till eternity. The report contains already 390K rows of itself (without the additional months of years which were missing). 

Do you also have an idea to work around this?

Example: 

In my case:
"Waiting for SAP (3) ........"