How to append with Power Query missing months to a table based on a month catalog table

[o59393]

Hi all

Is it possible to add to a table the missing months based from another date table catalog?

Let me explain myself better:

In the above image, I have data up to July 2020. However I would like to have the data included in all colunns up to the end of the month of my Date Table Catalog:

In this case, as seen the last date available is december 2020. 

Will it be possible with Power Query Editor, have the "BLY" table add the missing months and replicating all the column values from the already existing months?

My expected solution on Power BI would be to have the months and the rest of column values like this:

In the excel attached, I created a tab called "expected solution". It contains the existing values of table "BLY" + the missing months that would like to know if it's possible to have added with Power Query. 

Note: the rest of column values are exactly the same in any month. 

The reason why I need to have it with Power Query, it's because the original data source is linked to a global SQL database and I dont have the possibility to modify it and add manually the missing months.

I attach Pbix and excel.

https://1drv.ms/u/s!ApgeWwGTKtFdhxD2ZO8-o_R-ySs2?e=9hh5Az

Hopefully this is possible.

Thanks community.

[Fowmy]

@o59393 

I need to know, for example, if I need to add August 2020 based on existing data, on what basis should I follow? which month's data can be used and what if the month is not complete?

[amitchandak]

@Fowmy , why do need to missing month , you use date table and join with and then add +0 in formula ?

[Fowmy]

@amitchandak 

Thanks, 

I was concerned about this part "replicating all the column values from the already existing months", |
let's see what @o59393  comes back with.

[o59393]

Hi @Fowmy  and @amitchandak 

The basis should be the same for the non existing months like August 2020:

For example I sorted one SKU, plant and line. As you see they all have the share the values for mix and bpm for example. 

In yellow I highlighted the months that should be added hopefully with Power Query.

I added the values from full 2019 to the datasource if it helps in anything:

https://1drv.ms/u/s!ApgeWwGTKtFdhxGKCFr0n3R1vCvE?e=CFYfjH

Please let me know if it's clear or not.

Thanks.

[o59393]

Hi @Fowmy  and @amitchandak 

I managed to solve it. Fortunately I did a change to one of the sources and there was no need to touch the Power Query Editor.

Thanks!

[Fowmy]

@o59393 

Glad you found a solution!

If you are satisfied with my answer, please mark it as a solution so others can easily find it.

Don't forget to give KUDOS to replies that help answer your questions

Subscribe to ExcelFort: Learn Power BI, Power Query and Excel