Incorporate dates from a Calendar Table Query into another table

Luthius

Active Member
Joined
Apr 5, 2011
Messages
301
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
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Luthius

Active Member
Joined
Apr 5, 2011
Messages
301
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,778
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
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
301
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
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,778
suggestion from post#4 but with shared Excel file
show the logic for: Calendar Table dates (All)
 

Luthius

Active Member
Joined
Apr 5, 2011
Messages
301
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,778
You want to mix PQ with formula so I can't help
Maybe easier will be to write formula for column Result (x)
 

Watch MrExcel Video

Forum statistics

Threads
1,102,350
Messages
5,486,345
Members
407,541
Latest member
Emilybuhman

This Week's Hot Topics

Top