Filter table by values not contained in a list

[Anonymous]

Hi! I have the following script in M :

let
Source = Sql.Database("17x.Xx.xx.x", "XXX", [Query = "SELECT TOP 100#(lf)YEARWEEK_DATE,#(lf)YEARMONTH_DATE,#(lf)CONVERT(date, CONVERT(varchar(8), (yearmonth_date*100+01)), 112) as Periodo,#(lf)PDV_Dexter,#(lf)PDV_StockCenter,#(lf)PDV_Moov,#(lf)PDV_NikeShops,#(lf)PDV_eDexter,#(lf)PDV_eMeli,#(lf)PDV_eMoov,#(lf)PDV_ESTOCK_CENTER,#(lf)Mg_Pct_DX,#(lf)Mg_Pct_SC,#(lf)Mg_Pct_MV,#(lf)Mg_Pct_NSP,#(lf)CANAL ,#(lf)audit_datetime#(lf)FROM bi_tbl_tablero_stock#(lf)where#(lf)audit_datetime >=GETDATE()-5;"]),
#"Changed column type" = Table.TransformColumnTypes(Source, {{"branchid", type text}}),

New = #"Changed column type",

ListaDistintos = List.Distinct(New[YEARMONTH_DATE]),

FilteredHistory = Here I need to filter the "History" Table (was created before) in order to get all rows that  are not in "ListaDistintos" 

CombineOldNew = Table.Combine({FilteredHistory,New})
in
CombineOldNew

For example, If ListaDistintos has values (202204, 202201, 20211) and Current History Table has ( 202204, 202203, 202202, 202201, 202112, 20211) I need: History Table = (202203, 202202,202112). How could it be done?

Thanks in advance!

[Anonymous]

Using the List.Difference(CurrentHistory,ListaDistintos) function probably solves your problem.
If you don't know how to adapt it to your case, publish a table with your data (even fake ones) and explain in detail what you want to achieve

let
    ListaDistintos = {202204, 202201, 20211},
    CurrentHistory ={202204, 202203, 202202, 202201, 202112, 20211},
    History=List.Difference(CurrentHistory,ListaDistintos)
in
    History

[Anonymous]

You could also just turn your list into a table and use an Left or Right Anti join (depending on which is your left vs right table).

--Nate

[Anonymous]

I agree with not List.Contains(NameOfListWithValuesToExclude, [ColumnToFilter])

Which is to say, return the rows that do not contain these values in this column.

Also, be sure to

List.Buffer(List.Distinct(

so that your query is nice and fast and folding.

--Nate

--Nate

[ronrsnfld]

Here is an example of one method:

let
   ListaDistintos={202204,202201,202211},
   #"Current History Table" = Table.FromColumns(
       {{202204, 202203, 202202, 202201, 202112, 202211}}, {"Date?"}),
    #"Filtered History" = Table.SelectRows(#"Current History Table", 
            each not List.Contains(ListaDistintos,[#"Date?"]))
in  #"Filtered History"

[Anonymous]

Hi,@ronrsnfld , I have to use Listcontains() or List.ContainsAny() ?... I´m comparing a list of values, vs lot of values, not only one... ???

[ronrsnfld]

No. You compare each entry in the column, one at a time, to the contents of your List.  What happened when ran the code I posted?  Here it returned the items you specified in your question.

[HotChilli]

Add a step in Power Query with Table.SelectRows using List.Contains passing the list and column names as parameters.

List.Contains returns true if the item is in the list so you want to put a "not" in front of it.

Let me know if that is enough to help you.

[Anonymous]

Hi @HotChilli ...but list contains is used to find a value, not different values...shouldn't be, list.containsany? I don´t know how final filteredHistory line would be....