Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Many to many join question

I have two tables that have a many to many relationship. I want to flag in table 1 if the key appears atleast 1 in table 2.

 

Example:

Table 1

 

ID Value

1     A

1     B

2     C

2     C

3     D

 

Table 2:

 

ID Value2

1     Ma

1    NA

3    SD

3    ED

4    SJ

5   WS

 

I want my output table which is essentially table 1 and a flag added to it if that ID ever appeared in table 2

ID Value Flag

1     A    True

1     B     True

2     C

2     C

3     D    True

 

Is this possible to do in power BI?

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    MFelix jeremyking77 

     

    Thank you both for your suggestions. Unfortunately, it did not work as it threw me an error saying:

    "A single value for Table 1[ID] was not found".

     

    Meanwhile, this is what I did -

    I created a third table that had distinct values of Table 2 ID

    I created a relationship between Table 1 IDs and the IDs listed in new table. This would now be a many to one join and will reflect if that ID exists or not.

     

    Thank you,

    HV

3 Replies

  • jeremyking77's avatar
    jeremyking77
    Icon for Helper I rankHelper I
    6 years ago

    Yep thats possible

     

    Basically you can create a calculated column on the Table

     

    Flag = If(COUNTROWS(FILTER(Table2,Table2[ID] = Table1[ID])) > 0 ,"True","")

     

     

    You can also do this in Power Query using Merge Queries

     

    let
....
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, Table2, {"ID"}, "Table2", JoinKind.LeftOuter),
    #"Aggregated Table2" = Table.AggregateTableColumn(#"Merged Queries", "Table2", {{"ID", List.NonNullCount, "Count (Not Blank) of Table2.ID"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Aggregated Table2", "Flag", each if [#"Count (Not Blank) of Table2.ID"] > 0 then true else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Count (Not Blank) of Table2.ID"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Flag", "FlagUsingPowerQuery"}})
in
    #"Renamed Columns"

     

    Ive provided an example pbix file here

    https://mega.nz/#!2BBUxCpS!p2sBSWQPOQRyCicUYsVOmgZlewfvWhhggP63R6LBNAo 

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    6 years ago

    Hi Anonymous ,

     

    You can create the folllowing measure to add to a table visual:

    Check ID= IF(LOOKUPVALUE(Table2[ID];Table2[ID];SELECTEDVALUE(Table1[ID]))=BLANK(); ""; "True")

    Regards

    MFelix 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      MFelix jeremyking77 

       

      Thank you both for your suggestions. Unfortunately, it did not work as it threw me an error saying:

      "A single value for Table 1[ID] was not found".

       

      Meanwhile, this is what I did -

      I created a third table that had distinct values of Table 2 ID

      I created a relationship between Table 1 IDs and the IDs listed in new table. This would now be a many to one join and will reflect if that ID exists or not.

       

      Thank you,

      HV