Create custom column in one table looking on other table

[maximus84]

Hello. I am new in Power Bi

I have two tables (Table_A and Table_B) with many-to-many relations by “fk_id” fields.

The “id” is the primary key in Table_B. I want to create a custom field “id” in Table_A witch will be a foreign key to Table_B (many-to-one relations). As a result, I need to update each row of Table_A looking at Table_B.

The creation (updating) rule:

Table_A.id := Table_B.id WHERE Table_A.fk_id=Table_B.fk_id AND Table_A.date BETWEEN (Table_B.date_start;Table_B.date_end)

I need help to creating the DAX script witch will solve my problem.

Thank you very much!

Table_A

fk_id	date	id
1	01.01.2019	1
1	13.05.2019	2
1	26.08.2019	NULL
2	02.03.2019	3
3	02.02.2019	NULL
3	06.05.2019	4
4	03.03.2019	5
4	07.04.2019	5

Table_B

id	fk_id	date_start	date_end
1	1	01.01.2018	01.02.2019
2	1	02.02.2019	10.06.2019
3	2	01.01.2019	01.01.2020
4	3	03.03.2019	10.10.2019
5	4	01.01.2019	01.05.2019
6	4	02.05.2019	01.01.2020

[AnkitBI]

In Power Query, you can achieve using below. #"Added Custom" is using trick I learn from @Zubair_Muhammad 

let
    Source = Table.NestedJoin(Table_A, {"fk_id"}, Table_B, {"fk_id"}, "Table_B", JoinKind.Inner),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each let checkdate = [date] in Table.SelectRows([Table_B], each [date_start] < checkdate and [date_end] > checkdate)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"id"}, {"id"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Table_B"})
in
    #"Removed Columns"

Thanks
Ankit Jain
Do Mark it as solution if the response resolved your problem. Do Kudo the response if it seems good and helpful.

[maximus84]

Thanx a lot, but I got the problem after the first step - 

Table.NestedJoin(Table_A, {"fk_id"}, Table_B, {"fk_id"}, "Table_B", JoinKind.Inner)

After this, I have error msg -  "Expression.Error: A cyclic reference was encountered during evaluation."

Can you explain it?

[AnkitBI]

It is working for me. Can you share your PBIX file or all source queries.