how to join tables

[guilhem]

Hello,

I have the following tables I would like to make a new table as per follows

select 

  Issue ID

  Unit Name

  Cap

  Date

From

  Sheet1 join Sheet2, Sheet3

Where

  (Sheet3.Unit ID = Sheet2.Unit ID) and

  ((Sheet1 Date >= Sheet3.Date Start) and (Sheet1 Date <= Sheet3.Date End))

Assuming Sheet3 is 

Issue ID	Unit ID	Date Start	Date End
1	1	01/11/2016	03/11/2016
2	2	10/11/2016	14/11/2016
3	1	25/11/2016	26/11/2016

Sheet 2 is

Unit ID	Unit Name	Cap
1	a	10
2	b	20
3	c	30
4	d	40
5	c	50

and Sheet 1 are all the calendar days for the november 2016

I am hoping to get :

Date	Issue ID	Unit ID	Cap
01/11/2016	1	a	10
02/11/2016	1	a	10
03/11/2016	1	a	10
10/11/2016	2	b	20
11/11/2016	2	b	20
12/11/2016	2	b	20
13/11/2016	2	b	20
14/11/2016	2	b	20
25/11/2016	3	c	30
26/11/2016	3	c	30

Anyone can help ? 

Thanks ... 

[dkay84_PowerBI]

My approach may not be the best, but here goes:

I would start with a merge as new query operation within the query editor.

You can perform all the select aspects after the merge (i.e. removing columns).

You join sheet2 and sheet3 on Unit ID, choosing the appropriate type of join.

Once you have this new merged query, merge it (as new query) with the sheet1 table using DateStart and Date as the fields, and choose the appropriate join type to return all rows from the fact table with only the matching rows from the date table.

Repeat this process, using the original merged query, but use the DateEnd field.

Append the two final merged tables (date start and date end).

Remove the columns you don't need.

[guilhem]

sorry ... i am not too sure what you are telling me to do there ... 

My knowldge of power bi is too low...

Could you be more precise may be ?

Rgds

[dkay84_PowerBI]

This is all done in the query editor.  Are you familiar with this part of Power BI (Get and Transform, formerly Power Query, in Excel)?

[guilhem · Accepted solution]

I finally got the solution ...

Make a new table ...

Table = Filter( Crossjoin ( table A, TableB ), cond1&&cond2&&cond3....)

And it works ... 

[guilhem]

I have writen the sql query ...

SELECT
`a`.`Date` AS `Date`,
`b`.`UnitID` AS `UnitID`,
`b`.`IssueID` AS `IssueID`
FROM
(`sheet1` `a`
JOIN `Sheet3` `b`)
WHERE
((`a`.`Date` >= `b`.`Date Start`)
AND (`a`.`Date` <= `b`.`Date End`))

I just need this in Power BI language 😞