Forum Discussion

o59393's avatar
o59393
Post Prodigy
6 years ago

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

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.

 

6 Replies

  • 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's avatar
      amitchandak
      Super User

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

      • Fowmy's avatar
        Fowmy
        Super User

        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.