Incorporate dates from a Calendar Table Query into another table

Luthius

Active Member
Joined
Apr 5, 2011
Messages
324
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
ProductsDateValue
Product A31/01/202010
Product A18/03/202010
Product A21/02/202010


After - Result with the dates from my Calendar query table
ProductsCalendar Table dateValue
Product A01/01/202010
Product A02/01/202010
Product A03/01/202010
Product A04/01/202010
Product A05/01/202010
Product A06/01/202010
Product A07/01/202010
Product A...10
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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

ProductsDateFrequency of useValue
Product A31/01/20201 Month(s)10
Product B18/03/20203 Month(s)10
Product C21/02/20202 Week(s)10
 
Upvote 0
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
 
Upvote 0
calendarwith.png
 
Upvote 0
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"

ProductsDateFrequency of useValue
Product A31/01/20201 Month(s)10
Product B18/03/20203 Month(s)10
Product C21/02/20202 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

ProductsDate Calendar Table dates (All)ValueResult
...............
Product A10/01/20201 Month(s)10
Product B10/01/20203 Month(s)10
Product C10/01/20202 Week(s)10X
Product A11/01/20201 Month(s)10
Product B11/01/20203 Month(s)10
Product C11/01/20202 Week(s)10
Product A24/01/20201 Month(s)10
Product B24/01/20203 Month(s)10
Product C24/01/20202 Week(s)10X
...............
Product A31/01/20201 Month(s)10X
Product B31/01/20203 Month(s)10
Product C31/01/20202 Week(s)10
...............
Product C07/02/20202 Week(s)10X
Product A07/02/20201 Month(s)10
Product B07/02/20203 Month(s)10
Product C21/02/20202 Week(s)10X
 
Upvote 0
suggestion from post#4 but with shared Excel file
show the logic for: Calendar Table dates (All)
 
Upvote 0
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 <
 
Upvote 0
You want to mix PQ with formula so I can't help
Maybe easier will be to write formula for column Result (x)
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top