Many to many join question

[Anonymous]

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?

[MFelix]

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 · Accepted solution]

@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

[jeremyking77]

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