two tables fussion in a tabular table

[alexrf86]

Hi!

I need to fussion these 2 tables in a tabular table:

The first table represents my product catalog, with the product name, first date which the product was available for sale, last day the product was available for sale and last day the product was available for sale with today date for blanks.

The second table it is a calendar since my store opened doors: from the first day with available products until today.

I need to fussion these two tables, in such a way that calendar shows up the same day as many times as products were active that day. For instance, new table would display the following info from 04th March 2019

Day                Active Product

04/03/2019           a

05/03/2019           a

05/03/2019           b

06/03/2019           a

06/03/2019           b

May anyone help me please?

Thanks

[Mariusz]

Hi @alexrf86 

The attached file has a Table (referenced) that has an Added Column step in Power Query, you can adjust this to your needs.

Please follow the steps in the video.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[alexrf86]

Hi @Mariusz . Thanks for your answer. I was able to get the solution in your example:

However my product table was created by DAX functions and it is not available in Power Query. Would it be possible to do the same with DAX?

[Mariusz · Accepted solution]

Hi @alexrf86 

Please see the attached file with a solution or use the below table expression 

expanded = 
VAR __tbl =
    GENERATE(
        ALLNOBLANKROW( 'product'[product], 'product'[entry date], 'product'[leaving date] ),
        VAR __start = 'product'[entry date]
        VAR __end = COALESCE( 'product'[leaving date], TODAY() ) 
        RETURN 
            DATESBETWEEN( 'calendar'[Date], __start, __end )
    )
RETURN 
SELECTCOLUMNS(
    __tbl,
    "product", [product],
    "date", [Date] 
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Mariusz]

Hi @alexrf86 

Can you copy the first screenshot table and paste in the body of the message body as a reusable format?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[alexrf86]

@Mariusz  Please find attached my 2 power bi tables in a Excel file.

https://drive.google.com/open?id=1TizrhnqOFkQILfw-LViDkiWW9kjB8SNX 

Thanks for your help