Join two spreadsheets (or tables) using INNER JOIN and BETWEEN

Question

Hi everyone,

I am using Excel 2013 with Power Query and Power Pivot enabled. My spreadsheet contains data about transactions for a certain period (Period Begin Date - Period End Date):

I would like to transform data so that I get one row for each day in period from - to. For example, let's take the first row (transaction date = 12/04/2013). Expected result would be something like this:

Imagine this as joining table from picture one with table which contains list of all dates (DimDate).

Thanks in advance for any help provided,

Mike

Answer 1

Hi,

That is a nice solution.  In the table P_Apr, I see that the source has been obtained with the following statement

List.Dates(Table2[PStart]{0},Table2[DurDays]{0},#duration(1,0,0,0))

Has this come via simple recording or did you write this statement?

Answer 2

I managed to find a solution for this.

I created DimDate using instructions from this website. Then, I converted DimDate and my input data into tables using Power Query and after that I just created new excel connection and used following query:

SELECT ...

FROM data.xlsx.DimDate$ DimDate$, data.xlsx.Transactions$ Transactions$

WHERE Transactions$.Period Begin Date <= DimDate$.Date AND Transactions$.Period End Date >= DimDate$.Date

Thanks everyone!

Answer 3

Excel 2010 with free Power Query Add-In.

Compatible with Office 2013 Pro Plus.

Generate Date Tables with PQ.

http://www.mediafire.com/download/9dtqswm6cn8acm2/11_15_15.xlsx

Answer 4

Hi Ashish,

Thank you so much for the answer provided.

Although your solution gives expected result, it still requires a lot of manual work. For example:

• I probably need to do similar calculation for each record in initial 'table'
• What if there is a gap in periods between two 'rows' of data?

I really hoped there is some VBA script or Power Query trick to do this. I know how to do this in SQL Server but I don't want to use it in this case. In SQL Server I would create DimDate table with all dates on day level of granularity and then I would just need to join this DimDate table with my data as:

select X.TransactionDate, D.DateKey as PeriodDate

from InputData X

inner join DimDate D on D.DateKey between X.PeriodBeginDate and X.PeriodEndDate

This would "expand" my initial data and give me exactly the result I am after.

Thanks,

Answer 5

Hi,

Try this

1. In cell F2, type 1/4/2013
2. In cell F3, enter this and copy down

=F2+1

3. In ell E2, enter this formula and copy down

=INDEX($A$2:$A$6,MATCH(F2,$B$2:$B$6,1),1)

Hope this helps.

Here's a screenshot