Forum Discussion

maximus84's avatar
maximus84
Frequent Visitor
6 years ago

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 Ta...
Need Help
AnkitBI's avatar
AnkitBI
Solution Sage
6 years ago

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's avatar
    maximus84
    Frequent Visitor
    6 years ago

    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's avatar
      AnkitBI
      Solution Sage
      6 years ago

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