Forum Discussion
Create custom column in one table looking on other table
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 |
3 Replies
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.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?
It is working for me. Can you share your PBIX file or all source queries.
