[PQ/Power BI] - Assigning a phase from another queries date range

[Dutsj]

I have two queries where i can merge them with the "ID". The phase query has the phase ID, and the phase start and end date. The other query has hour values and a date value, and i want to add a column to this query with the Phase name, depending on which phase date range the date value is in. See below tables (or attached image with colours for better explanation). I'm using Power BI so i'd like to be able to do this in either Power BI or Power Query. I hope it makes sense and that someone knows how to solve this

Phase query
ID	Phase Name	Phase Start date	Phase end date
1​	A	01-01-2022​	31-01-2022​
1​	B	01-02-2022​	28-02-2022​
2​	B	01-02-2022​	28-02-2022​
3​	A	01-03-2022​	20-03-2022​
3​	B	01-04-2022​	15-04-2022​

Hour query			All i need to get my result is to add the phase here with a merge or similar
ID	Hours	Date	Phase
1​	2​	05-01-2022​	?
1​	3​	07-02-2022​	?
2​	2​	10-02-2022​	?
2​	10​	01-01-1901​	?
3​	5​	05-03-2022​	?
3​	8​	02-04-2022​	?
3​	3​	08-04-2022​	?

Result (visualisation)
ID	Hours (combined)	Phase
1​	2​	A
1​	3​	B
2​	2​	B
3​	5​	A
3​	11​	B

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="PhaseTable"]}[Content],
    PhaseQuery = Table.TransformColumnTypes(Source,{{"Phase Start date", type date}, {"Phase end date", type date}}),
    tbl1 = Excel.CurrentWorkbook(){[Name="HoursTable"]}[Content],
    HoursQuery = Table.TransformColumnTypes(tbl1,{{"Date", type date}}),
    AddPhaseName = Table.AddColumn(HoursQuery, "Phase Name", each 
                    Text.Combine(Table.SelectRows(PhaseQuery, (x)=> x[ID] = _[ID] and _[Date]>= x[Phase Start date] and _[Date] <= x[Phase end date])[Phase Name], ", ")),
    RemoveDateColumn = Table.RemoveColumns(AddPhaseName,{"Date"})
in
    RemoveDateColumn

Book1
	A	B	C	D	E	F	G	H	I
1	ID	Phase Name	Phase Start date	Phase end date
2	1	A	1/1/2022	1/31/2022
3	1	B	2/1/2022	2/28/2022
4	2	B	1/2/2022	2/28/2022
5	3	A	1/3/2022	3/20/2022
6	3	B	1/4/2022	4/15/2022
7
8
9	ID	Hours	Date			ID	Hours	Phase Name
10	1	2	1/5/2022			1	2	A
11	1	3	2/7/2022			1	3	B
12	2	2	2/10/2022			2	2	B
13	2	10	1/1/1901			2	10
14	3	5	3/5/2022			3	5	A, B
15	3	8	4/2/2022			3	8	B
16	3	3	4/8/2022			3	3	B
17
Sheet1

 

[Dutsj]

let
    Source = Excel.CurrentWorkbook(){[Name="PhaseTable"]}[Content],
    PhaseQuery = Table.TransformColumnTypes(Source,{{"Phase Start date", type date}, {"Phase end date", type date}}),
    tbl1 = Excel.CurrentWorkbook(){[Name="HoursTable"]}[Content],
    HoursQuery = Table.TransformColumnTypes(tbl1,{{"Date", type date}}),
    AddPhaseName = Table.AddColumn(HoursQuery, "Phase Name", each
                    Text.Combine(Table.SelectRows(PhaseQuery, (x)=> x[ID] = _[ID] and _[Date]>= x[Phase Start date] and _[Date] <= x[Phase end date])[Phase Name], ", ")),
    RemoveDateColumn = Table.RemoveColumns(AddPhaseName,{"Date"})
in
    RemoveDateColumn

Book1
	A	B	C	D	E	F	G	H	I
1	ID	Phase Name	Phase Start date	Phase end date
2	1	A	1/1/2022	1/31/2022
3	1	B	2/1/2022	2/28/2022
4	2	B	1/2/2022	2/28/2022
5	3	A	1/3/2022	3/20/2022
6	3	B	1/4/2022	4/15/2022
7
8
9	ID	Hours	Date			ID	Hours	Phase Name
10	1	2	1/5/2022			1	2	A
11	1	3	2/7/2022			1	3	B
12	2	2	2/10/2022			2	2	B
13	2	10	1/1/1901			2	10
14	3	5	3/5/2022			3	5	A, B
15	3	8	4/2/2022			3	8	B
16	3	3	4/8/2022			3	3	B
17
Sheet1

Looks great! I had some issues with my initial setup but i fixed it, thanks. I had no idea you could have multiple tables loaded into a single query like that and then referencing them backwards, thanks!

 

[Dutsj]

let
    Source = Excel.CurrentWorkbook(){[Name="PhaseTable"]}[Content],
    PhaseQuery = Table.TransformColumnTypes(Source,{{"Phase Start date", type date}, {"Phase end date", type date}}),
    tbl1 = Excel.CurrentWorkbook(){[Name="HoursTable"]}[Content],
    HoursQuery = Table.TransformColumnTypes(tbl1,{{"Date", type date}}),
    AddPhaseName = Table.AddColumn(HoursQuery, "Phase Name", each
                    Text.Combine(Table.SelectRows(PhaseQuery, (x)=> x[ID] = _[ID] and _[Date]>= x[Phase Start date] and _[Date] <= x[Phase end date])[Phase Name], ", ")),
    RemoveDateColumn = Table.RemoveColumns(AddPhaseName,{"Date"})
in
    RemoveDateColumn

Book1
	A	B	C	D	E	F	G	H	I
1	ID	Phase Name	Phase Start date	Phase end date
2	1	A	1/1/2022	1/31/2022
3	1	B	2/1/2022	2/28/2022
4	2	B	1/2/2022	2/28/2022
5	3	A	1/3/2022	3/20/2022
6	3	B	1/4/2022	4/15/2022
7
8
9	ID	Hours	Date			ID	Hours	Phase Name
10	1	2	1/5/2022			1	2	A
11	1	3	2/7/2022			1	3	B
12	2	2	2/10/2022			2	2	B
13	2	10	1/1/1901			2	10
14	3	5	3/5/2022			3	5	A, B
15	3	8	4/2/2022			3	8	B
16	3	3	4/8/2022			3	3	B
17
Sheet1

I spoke too early, unfortunately my dataset is too large and it freezes when i do it with the whole dataset, i tried letting it load but gave up after an hour... Any suggestions on how to make it lighter?

 

[JGordon11]

try wrapping the two main tables (in the example PhaseQuery and HoursQuery) in Table.Buffer

like this for the example:

let
    Source = Excel.CurrentWorkbook(){[Name="PhaseTable"]}[Content],
    PhaseQuery = Table.Buffer(Table.TransformColumnTypes(Source,{{"Phase Start date", type date}, {"Phase end date", type date}})),
    tbl1 = Excel.CurrentWorkbook(){[Name="HoursTable"]}[Content],
    HoursQuery = Table.Buffer(Table.TransformColumnTypes(tbl1,{{"Date", type date}})),
    AddPhaseName = Table.AddColumn(HoursQuery, "Phase Name", each 
                    Text.Combine(Table.SelectRows(PhaseQuery, (x)=> x[ID] = _[ID] and _[Date]>= x[Phase Start date] and _[Date] <= x[Phase end date])[Phase Name], ", ")),
    RemoveDateColumn = Table.RemoveColumns(AddPhaseName,{"Date"})
in
    RemoveDateColumn

 

[Dutsj]

try wrapping the two main tables (in the example PhaseQuery and HoursQuery) in Table.Buffer

like this for the example:

let
    Source = Excel.CurrentWorkbook(){[Name="PhaseTable"]}[Content],
    PhaseQuery = Table.Buffer(Table.TransformColumnTypes(Source,{{"Phase Start date", type date}, {"Phase end date", type date}})),
    tbl1 = Excel.CurrentWorkbook(){[Name="HoursTable"]}[Content],
    HoursQuery = Table.Buffer(Table.TransformColumnTypes(tbl1,{{"Date", type date}})),
    AddPhaseName = Table.AddColumn(HoursQuery, "Phase Name", each
                    Text.Combine(Table.SelectRows(PhaseQuery, (x)=> x[ID] = _[ID] and _[Date]>= x[Phase Start date] and _[Date] <= x[Phase end date])[Phase Name], ", ")),
    RemoveDateColumn = Table.RemoveColumns(AddPhaseName,{"Date"})
in
    RemoveDateColumn

Amazing, thank you so much!

Does the table.buffer make sure that it does the previous calculations first and then compares the dates instead of doing the calculations everytime it compares the dates?

 

[JGordon11]

Table.Buffer - PowerQuery M

Learn more about: Table.Buffer

docs.microsoft.com