Add missing month rows based on multiple columns with multiple variables in Power BI

[FelixN]

I'm very stuck and I was hoping you can help. So have the following dataset (Table 1) with Month (5 years worth), Customer (1000 customers), Product (100 products), Units and Value (value is just unit multiple a price). The data only shows rows with unit and value, so for customers when there is no sale in a month, there is no data. 

I want to create a table (Table 2) where every product for every customer is shown for all time periods, where actual units and values are included and those missing in Table 1 are now showing 0.

I have read many posts here and elsewhere, which only handles 1 column (e.g. only Customer not both Customer AND Product, and only 1 measure not Unit and Value). I tried to adapt the code but failed miserably.

I also want to do this in Power BI using M not DAX, because I would like to further transform the data.

It will also be fantastic if you can help by adding any explanations to help us learn. Thank you so much everyone!!!

[ashwinkolte]

Dear Amit

I am new to Power BI and a begineer .

I have almost exact  problem to solve .  However the below formula you suggested is not working . It shows red underline from the summarize portion as I try to use it (off course with my table / column names ). I assume we are creating a table with the below formula

 Union(
Addcolumns(
except(
GENERATE(GENERATE(distinct(Table[Customer]), distinct(Table[Product])), calendar(date(2017,01,01), Date(2022,12,31)))
summarize(Table, Table[Customer], Table[Product], Table[Date])), "Units", 0, "Value", 0),
Table) 

Hoping you can help !

Thanks

Ashwin

[amitchandak · Accepted solution]

@FelixN , Try a new table like

Union(
Addcolumns(
except(
GENERATE(GENERATE(distinct(Table[Customer]), distinct(Table[Product])), calendar(date(2017,01,01), Date(2022,12,31)))
summarize(Table, Table[Customer], Table[Product], Table[Date])), "Units", 0, "Value", 0),
Table)

use selectcolumns if needed for rename

But I would suggest , check if approch used in video can help

Power BI Items/Categories not sold to the customers: https://youtu.be/AbuKvAnicwo

[FelixN]

Thank you @amitchandak