cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
maximus84 Frequent Visitor
Frequent Visitor

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_iddateid
101.01.20191
113.05.20192
126.08.2019NULL
202.03.20193
302.02.2019NULL
306.05.20194
403.03.20195
407.04.20195

 

Table_B

idfk_iddate_startdate_end
1101.01.201801.02.2019
2102.02.201910.06.2019
3201.01.201901.01.2020
4303.03.201910.10.2019
5401.01.201901.05.2019
6402.05.201901.01.2020
3 REPLIES 3
Highlighted
AnkitBI Established Member
Established Member

Re: Create custom column in one table looking on other table

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 Frequent Visitor
Frequent Visitor

Re: Create custom column in one table looking on other table

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 Established Member
Established Member

Re: Create custom column in one table looking on other table

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 388 members 3,288 guests
Please welcome our newest community members: