Power Bi Remove Rows Based on Category found in a related table.

[RaymondWood]

Hi all.  I do a daily download of a CSV file with new tenders from a government site. I am only interested in particular categories of tenders so want to remove any rows with categories that I am not interested in. The two tables are connected by the category column. 

                       TendersData[Category] *----------1 CategoryData[Category]

Unfortunately there are 4,700 possible tender categories from which I have removed 4,400 of the categories that I don't care about. I am not going to add them all to a formula.

Okay so now I need Power Query in Power Bi to remove any rows from the TendersData table that do not contain one of the 300 categories found in the CategoryData table.

Yes I could just put a filter on the page (Which I do have) but it includes blanks(non-intersecting categories) data onto my visual.

[jsaunders_zero9]

Hi @RaymondWood 

You can filter the tenders table based on the values in the categories table, by turning the column into a list.

Try the following in Power Query.

Category Table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTrkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Category = _t])
in
    Source

Tenders Table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJUitWJVjKCs4yBLCcwywTIcgazTOEsMyDLVSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Tender = _t, Category = _t]),
    FilterCategories = Table.SelectRows(Source, each (List.Contains(Table.Column(Categories,"Category"),[Category])=true ))
in
    FilterCategories

[RaymondWood]

Thank you for you response. I don't know how to use what you have given me.

[jsaunders_zero9 · Accepted solution]

No problem.

1. Open PowerQuery
2. From the top menu select New Source > Blank Query
3. From the top menu select Advanced Editor and paste the Category Table code in.
4. Rename the query to Category Table
5. Repeat for the second table using the Tenders Table table, rename table as Tenders Table.

This will show you the steps that I have taken to solve your issue, if you have an specific questions let me know. Happy to point you in the right direction.

James

[RaymondWood]

Thank you so much James.