Incorporate dates from a Calendar Table Query into another table

[Luthius]

Guys
I have the following table in my spreadsheet and I want to incorporate every single date from a Calendar Table query into this table.


Before - ProductTable

Products	Date	Value
Product A	31/01/2020	10
Product A	18/03/2020	10
Product A	21/02/2020	10

After - Result with the dates from my Calendar query table

Products	Calendar Table date	Value
Product A	01/01/2020	10
Product A	02/01/2020	10
Product A	03/01/2020	10
Product A	04/01/2020	10
Product A	05/01/2020	10
Product A	06/01/2020	10
Product A	07/01/2020	10
Product A	...	10

 

[Luthius]

I forgot to mention that I will have some calculation using a FREQUENCY OF USE column. I mean, the Product A will repeat every Month using the date as per my Calendar Table.
For the Product B, the frequency of use is different, meaning every 3 months and so on.
I guess it will be necessary create some custom function that makes the calculation for the frequency (fncCalculateFrequency)

Searching about it I just found for Power BI and I don't know if I can implement it on Excel Power Query
Calculating Orders Frequency

Products	Date	Frequency of use	Value
Product A	31/01/2020	1 Month(s)	10
Product B	18/03/2020	3 Month(s)	10
Product C	21/02/2020	2 Week(s)	10

 

[Luthius]

anybody please

 

[sandy666]

Your example doesn't make sense
try to merge calendar and product by date with inner join
you will get the same dates

I suggest post representative example with two sources: part of calendar and product table and expected result based on these sources

 

[sandy666]

 

[Luthius]

Sorry guys for not proper formulate my question.
I will explain it in details.

I have a table of products where there is a column called "frequency of use"

Products	Date	Frequency of use	Value
Product A	31/01/2020	1 Month(s)	10
Product B	18/03/2020	3 Month(s)	10
Product C	21/02/2020	2 Week(s)	10

I want to incorporate/Join all dates from a Query table calendar into this table adding a new column generated by a Invoked M's language custom Function that when the date matches with the frequency, will receive an "x" value into this column. As you can see below, the date will repeat for each product. Its not a problem because when I generate the Pivot Table with the result of this query table I can consolidate as my needs.
... Means that the values continue

Products	Date	Calendar Table dates (All)	Value	Result
...	...	...	...	...
Product A	10/01/2020	1 Month(s)	10
Product B	10/01/2020	3 Month(s)	10
Product C	10/01/2020	2 Week(s)	10	X
Product A	11/01/2020	1 Month(s)	10
Product B	11/01/2020	3 Month(s)	10
Product C	11/01/2020	2 Week(s)	10
Product A	24/01/2020	1 Month(s)	10
Product B	24/01/2020	3 Month(s)	10
Product C	24/01/2020	2 Week(s)	10	X
...	...	...	...	...
Product A	31/01/2020	1 Month(s)	10	X
Product B	31/01/2020	3 Month(s)	10
Product C	31/01/2020	2 Week(s)	10
...	...	...	...	...
Product C	07/02/2020	2 Week(s)	10	X
Product A	07/02/2020	1 Month(s)	10
Product B	07/02/2020	3 Month(s)	10
Product C	21/02/2020	2 Week(s)	10	X

 

[sandy666]

suggestion from post#4 but with shared Excel file
show the logic for: Calendar Table dates (All)

 

[Luthius]

I'm sharing the sample file.
This file contains a sheet called PowerQuery-Table where as the name suggests, this is the way I want the dates of my custom calendar table join my Sample-Data table.
Don't worry too much about the sheet End-Result because once my PowerQuery-Table is created, I can manipulate the results on a Pivot Table.

Ps.: This is just a small sample of data. The Range of my Calendar is adjustable and there are more products in the real world Sample-Data table.

>Find file here <

 

[sandy666]

You want to mix PQ with formula so I can't help
Maybe easier will be to write formula for column Result (x)

 

[Luthius]

You want to mix PQ with formula so I can't help
Maybe easier will be to write formula for column Result (x)

I found something similar with Power BI Calculating Orders Frequency

But I don't know how to adapt it to my case.