filtering out records that don't exist in another

[jasonyeung87]

Hi,

I want to import data from a table. The data I want to import would only be imported if the ID field exist in another table. For example, Below is Table A, which I want to import from:

Issue ID	Issue Name
1	aa
2	bb
3	cc
4	dd
5	ee
6	ff
7	gg

It will only import the record if the ID is in Table B below:

Issue ID	Answer Name
1	pp
1	oo
3	ii
3	uu
5	yy
5	tt
6	rr

The end result would be:

Issue ID	Issue Name
1	aa
3	cc
5	ee
6	ff

Records 2, 4 and 7 are excluded because they are not in Table B. I was wondering what is the best approach for doing this? 

Sincerely,

Jason

[Anonymous · Accepted solution]

Hello @jasonyeung87 ,

Please use inner join to solve your requirements. It will only take the data field which matches in both the tables.

Power query editor -> Merge queries -> select ID field as common --> The result is what you expected.

Thanks,

Karthik

[v-rzhou-msft · Accepted solution]

Hi @jasonyeung87 

Please check whether you build many to one relationships between two tables, this issue may be caused by value changing in key column(relate with other columns.)

Here I couldn't reproduce your issue, but I will give you some advice and show you an other way by M code.

1. Merge as KarthikKV by inner is a good way, and I think you don't need to expand other table, you will use merge to filter your Table A. 

Then you just need to remove Table B to get result you want.

2. Try to use M query to filter "Table A" by Issue ID list in "Table B".

M Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hca3DQAwDAOwXzRnST/G8JD+/wcRxIlmiAgYAx4MiZ1TzexaamH3Vit7jtrYe9XOvgf3Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Issue ID" = _t, #"Issue Name" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Issue ID", Int64.Type}, {"Issue Name", type text}}),   
    #"Filtered Rows" =
    let  list1 = List.Distinct(#"Table B"[Issue ID]) in
    Table.SelectRows(#"Changed Type",each List.Contains(list1,[Issue ID]))
    in
    #"Filtered Rows"

Just copy codes #"Filter Row" ... into your Advance Editor in Table A.

Result:

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[jasonyeung87]

Hi @v-rzhou-msft ,

Thanks for your tips. Yeah, I didn't need to expand the column of the second table if I only want filtering. I think what was causing the issue is that because the second table has multiple records with the same ID, it causes the error when it's expanded

Sincerely,

Jason

[Ashish_Mathur]

Hi,

Do you want to do this in the Query Editor or directly in your table visual?

[Anonymous · Accepted solution]

Hello @jasonyeung87 ,

Please use inner join to solve your requirements. It will only take the data field which matches in both the tables.

Power query editor -> Merge queries -> select ID field as common --> The result is what you expected.

Thanks,

Karthik

[jasonyeung87]

Hi @Anonymous ,

thanks for the info. I initially did this and it worked. But if I expanded the column I imported (e.g. picked 1 column of the second table to include), I get the error:

Column 'Issue ID' in Table '' contains a duplicate value '' and this is not allowed for columns on the one side of a many-to-one relationship or for columns that are used as the primary key of a table.

I probably can work with this if I don't expand the column.

[Anonymous]

Hello @jasonyeung87 ,

Not sure as I never dealed much with many to many relationships.

Let me know if you have solved it so that I can also understand.

Thanks,

Karthik

[jasonyeung87]

Hi @Anonymous ,

When I reviewed the record set closer, the Inner join actually does what I want (e.g.  to filter out the records). I realised I didn't have to expand the column, as this column doesn't appear when I exit Power Query editor. So the solution works for filtering.

If I need data from that table itself, I could use the data modeler instead.

Jason