Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.