Delete rows if the value is included in another column

[Anonymous]

Hello

What is the Power Query to delete rows where a column has:

1) a specific value 'something'

2) a specific value that is in a column of a different table

In the 2), will it trigger the update of the different table before it refreshes the current table?

Thanks!

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

Table2:

You may insert a new step with the following m codes for 'Table1'.

= Table.SelectRows(#"Changed Type",each not List.Contains(Table2[Col1],[Col1]) and not List.Contains({64,52},[Col2]))

Here are the codes in 'Advanced Editor' for 'Table1'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRS0lEyMlGK1YlWcg4Csk2NwWx3NyDbxBTMDgoBss2AamIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", Int64.Type}}),
    Custom1 = Table.SelectRows(#"Changed Type",each not List.Contains(Table2[Col1],[Col1]) and not List.Contains({64,52},[Col2]))
in
    Custom1

Result:

Best Regards

Allan

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

[edhans]

Can you provide sample data @Anonymous ? I could tell you how to do it, but without data this is hard. 

To do it you do a conditional merge with another table that has the value of "something" from the other table.

I suspect that isn't very helpful, and it may not even be right as I may be misunderstanding your question with no data and expected output to see.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[Anonymous]

@edhans sure, I have these tables:

Table1

AB, 24

CR, 53

GF, 45

RT, 64

Table2

GR

GF

YT

When I connect/refresh to Table1, I want not to load the following rows:

Col2 = 64 or 52

Col1 = contained in the values of Table2[Col1]

So the output would be:

AB, 24

CR, 53

Hope this helps?

[edhans]

That isn't working but I think you have some typos.

1. There is no 52 in any table.
2. Table 2 has YT and Table 1 has RT, so RT stays.

Look at this code. It is a bit messy because I had to clean up your initial posting. Please, if you need further help with data, please use the links below to properly post data so it is a relatively clean table.

This is the first table. It does all of the work. It removes data in this table by doing a Left Anti-join against Table 2.

Call this First Table when you paste it as a query (instructions below)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnTSUTAyUYrViVYCE85BOgqmxgi+u5uOgokpgh8UoqNgBlQfCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1.1", "Letters"}, {"Column1.2", "Numbers"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns", {"Letters"}, #"Second Table", {"Letters"}, "Second Table", JoinKind.LeftAnti),
    #"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Second Table"})
in
    #"Removed Columns"

This is table 2. Call this Second Table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg9SitWJVgIT7m4IdmSIUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> "")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1", "Letters"}})
in
    #"Renamed Columns"

So this:

becomes this:

because this is what was in the second table:

This is how the Join works when you do the merge:

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.