Generating data when there is no sale

[Anonymous]

Hello everyone,

I'm working with a model that contains information about sales of different products in different centers. The fact table of sales contains data only for the days that a product has been sold in a center, which means that I have no info if a product has not been sold in a day. I need to "physically" have data for every day that this happens, so instead of not having a row, I would have a row containing the information of that day, the center, the product and quantity 0. 

I know I can use "Show items with no data" in the visual object, but that's not what I'm looking for since I need to have the rows in the table. 

An example of what I'm saying is this:

Current info:

Date (dd/mm/yyyy)	Center	Product	Qty
01/01/2020	A	P1	15
01/01/2020	A	P2	7
02/01/2020	A	P1	9
03/01/2020	A	P1	10
03/01/2020	A	P2	6

Desired info:

Date (dd/mm/yyyy)	Center	Product	Qty
01/01/2020	A	P1	15
01/01/2020	A	P2	7
02/01/2020	A	P1	9
02/01/2020	A	P2	0
03/01/2020	A	P1	10
03/01/2020	A	P2	6

As you can see, I added info with Qty 0 for the Center "A", Product "P2" on day "02/01/2020". This is what I'm trying to automatically add.

Do you guys know how can I make this in PBI?

Thank you in advance.

[mahoneypat · Accepted solution]

This can be done in the query editor as follows.

Create a list of dates with List.Dates spanning the range you need and convert it to a table

Add a column with a table of your Product and Centers, and expand it

At this point you'll have a row for every combinations of Date and Product/Center

Merge your actual data table into this new table on Date and Product columns

Expand the merged table

Replace nulls (rows that didn't have a match) with zeros

Regards,

Pat

[Anonymous]

Thank you very much @mahoneypat , that was it 🙂 

Kind regards

[amitchandak]

@Anonymous , You can add +0 to the measure

like sum(Table[Qty]) +0

or use this option - Show Item with No data

[Anonymous]

Hello @amitchandak ,

Thank you for your reply. As I said, I know I can use the "show items with no data" option, but it's not what I'm looking for. I'm actually trying to generate the rows in the dataset, so I have physical rows for every day that has no sale.

[PaulDBrown]

@Anonymous 

Both the methods suggested by @EmaVasileva @  and @amitchandak work, but you need to use a date table  with continuous dates covering the range of dates in your fact table (not the date from your fact table)  in your visual.

Here is the model as I have it, and highlighted in red are the dimension tables I've used to create the table visual. The measures are according to @EmaVasileva  and @amitchandak  suggestions:

[Anonymous]

Hello @PaulDBrown ,

I know both solutions work, but that's not what I'm looking for since that information only "exists" in the visual object, not in the fact table. What I'm trying to do is actually create those rows in my dataset.

Thank you.

[PaulDBrown]

@Anonymous 

Just out of curiosity, is there any reason in particular that you need the physical rows? 

[Anonymous]

Hello @PaulDBrown ,

Yes, I know it may sound odd, but I need the physical rows in order to do some calculation the client wants. 

[PaulDBrown]

@Anonymous 

In that case, I would follow @mahoneypat  suggestion to create the rows in Power Query

[EmaVasileva]

Hi @Anonymous,

Please take a look on this article: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data

And I think once you have a row with data with 'Blank' Product Qty, you can use the following formula: 

IF (CALCULATE ( SUM ( Product Qty) ) = BLANK (),0, CALCULATE ( SUM ( Product Qty) ))

This will return 0 when you have a Blank.

Please let me know if this works.

Best,
Ema

[Anonymous]

Hello @EmaVasileva ,

Thank you for your reply. I know I can do that, but that's not a valid solution since, as I said, I need to physically have those rows in my dataset. Your solution generates those rows for the visual object where you are using it but they don't exist in the dataset below.