Help filtering in Advanced Editor

Hi Anonymous ,

I guess because you must mark it as last step in your code :).

#"filter rows" =Table.SelectRows(#"C:\Users\60047182\Desktop\MARK\Producción\Archivos de produccion\_produccin-de-pozos-de-gas-y-petrleo-2006 csv","prod_pet">0,"prod_gas">0)
in
#"filter rows"

Best,

Kathrin

If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!

Hi Anonymous ,

I got your CSV file. 290MB, over 940,000 records. Given the critera you gave of Pet_Prod > 0 and Pet_Gas > 0 it reduced it to 215,000 records, which took less than 20 seconds here to load in to the data model.

The query I used was this:

let
    Source = Csv.Document(File.Contents("C:\Users\Ed Hansberry\OneDrive - eHansalytics\Downloads\produccin-de-pozos-de-gas-y-petrleo-2019.csv"),[Delimiter=",", Columns=38, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"idempresa", type text}, {"anio", Int64.Type}, {"mes", Int64.Type}, {"idpozo", Int64.Type}, {"prod_pet", type number}, {"prod_gas", Int64.Type}, {"prod_agua", type number}, {"iny_agua", type number}, {"iny_gas", Int64.Type}, {"iny_co2", Int64.Type}, {"iny_otro", Int64.Type}, {"tef", type number}, {"vida_util", Int64.Type}, {"tipoextraccion", type text}, {"tipoestado", type text}, {"tipopozo", type text}, {"observaciones", type text}, {"fechaingreso", type datetime}, {"rectificado", type text}, {"habilitado", type text}, {"idusuario", Int64.Type}, {"empresa", type text}, {"sigla", type text}, {"formprod", type text}, {"profundidad", type number}, {"formacion", type text}, {"idareapermisoconcesion", type text}, {"areapermisoconcesion", type text}, {"idareayacimiento", type text}, {"areayacimiento", type text}, {"cuenca", type text}, {"provincia", type text}, {"tipo_de_recurso", type text}, {"proyecto", type text}, {"clasificacion", type text}, {"subclasificacion", type text}, {"sub_tipo_recurso", type text}, {"fecha_data", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([prod_pet] > 0) and ([prod_gas] > 0))
in
    #"Filtered Rows"

I did that 100% in the Power Query UI model. No advanced coding at all. Took just a few seconds. Your Table.SelectRows statement had the wrong syntax and too many arguments. You can see the correct syntax above.

I strongly recommend until you get a really good handle on M code you use the UI. I have a decent handle on M code and I rarely hand-craft a Table.SelectRows() statement from scratch. Even if I need it in a structured column, I'll often create the core statement with the UI then copy and paste the code where I need it. M is case sensitive and even with Intellisense it isn't the easist language to just type out.