Using SQL Statement in advance option for sql databse connector

[AlekseiDiazPUCP]

Hello eveyone

I am using this connecction options. The ip address and database name are not the real ones. but the problem I got is for the sql query when I try to filter based on a string column. I mean it works fine with just 

[AÑO_VIAJE] >= 2023 but it will not work if I add the other conditions

SELECT [NOM_MATERIAL],[NOM_MATERIAL_TIPO] FROM [dbo].[tabla]
WHERE [AÑO_VIAJE] >= 2023 AND [NOM_MATERIAL] = "MATERIAL 1"

it wont work either with 

SELECT [NOM_MATERIAL],[NOM_MATERIAL_TIPO] FROM [dbo].[tabla]
WHERE  [NOM_MATERIAL] = "MATERIAL 1"

if both cases the same error message is returned

I do not know what is treating the "Material 1" as a column

in the formula editor the connection is like this

= Sql.Database("000.0.0.0", "database", [CommandTimeout=#duration(0, 6, 0, 0), Query="SELECT [NOM_MATERIAL],[NOM_MATERIAL_TIPO] FROM [dbo].[REPORTE_VENTA_COMERCIAL]#(lf)WHERE [NOM_MATERIAL] = ""MATERIAL 1"""])

[AlekseiDiazPUCP]

Thanks @BA_Pete 

This has been quite helpful

1. resolved my issue! Thanks!1

2. what you mean by naative query? I used no advance option in the connections and try to filter the year as per your suggestion but not sure about the last part you talk about, Would you mind provide furthe details

[BA_Pete]

Check all the steps. Sometimes the way query folding works is that not every step will fold on its own but, once the native query has been generated and optimised, the entire query will fold. This would be seen on the final query step.

If none of your steps are folding, then copy all the code out ofAdvanced Editor and paste it into a code window here ( </> button ). I should be able to see what's preventing query folding.

Pete

[AlekseiDiazPUCP]

Hello sir

I might have found the source of my problems within this article

https://learn.microsoft.com/en-us/power-query/native-query-folding

anyways I will drop my code

let
    Origen = Sql.Database("000.1.8.00", "database", [CommandTimeout=#duration(0, 6, 0, 0)]),
    dbo_REPORTE_VENTA_COMERCIAL = Origen{[Schema="dbo",Item="REPORTE_VENTA_COMERCIAL"]}[Data],
    #"Filas >=2023 filtradas" = Table.SelectRows(dbo_REPORTE_VENTA_COMERCIAL, each [AÑO_VIAJE] >= 2023),
    #"fecha de viaje agregada" = Table.AddColumn(#"Filas >=2023 filtradas", "FECHA_VIAJE", each #date([AÑO_VIAJE],[MES_VIAJE],[DIA_VIAJE])),
    #"fecha de venta agregada" = Table.AddColumn(#"fecha de viaje agregada", "FECHA_VENTA", each #date([AÑO_VENTA],[MES_VENTA],[DIA_VENTA])),
    #"fecha de viaje emision agregada" = Table.AddColumn(#"fecha de venta agregada", "FECHA_VIAJE_EMISION", each #date([AÑO_VIAJE_EMISION],[MES_VIAJE_EMISION],[DIA_VIAJE_EMISION])),
    #"Errores reemplazados" = Table.ReplaceErrorValues(#"fecha de viaje emision agregada", {{"FECHA_VIAJE_EMISION", null}}),
    #"Tipo cambiado con configuración regional" = Table.TransformColumnTypes(#"Errores reemplazados", {{"FECHA_VIAJE", type date}, {"FECHA_VENTA", type date}, {"FECHA_VIAJE_EMISION", type date}}, "es-PE"),
    #"Columna condicional agregada" = Table.AddColumn(#"Tipo cambiado con configuración regional", "ETAPA_VIDA_VIAJERO", each if [NUM_PASAJERO_EDAD] = null then "Sin data de edad del viajero" else if [NUM_PASAJERO_EDAD] <= 12 then "Infante (0 a 12 años)" else if [NUM_PASAJERO_EDAD] <= 18 then "Adolescente (13 a 18 años)" else if [NUM_PASAJERO_EDAD] <= 25 then "Joven (19 a 25 años)" else if [NUM_PASAJERO_EDAD] <= 40 then "Adulto joven (26 a 40 años)" else if [NUM_PASAJERO_EDAD] <= 50 then "Adulto intermedio (41 a 50 años)" else if [NUM_PASAJERO_EDAD] <= 60 then "Adulto tardío (51 a 60 años)" else if [NUM_PASAJERO_EDAD] >= 61 then "Senior (61 años a más)" else null),
    #"Columna condicional agregada1" = Table.AddColumn(#"Columna condicional agregada", "Orden_Etapa_vida_viajero", each if [ETAPA_VIDA_VIAJERO] = "Infante (0 a 12 años)" then 1 else if [ETAPA_VIDA_VIAJERO] = "Adolescente (13 a 18 años)" then 2 else if [ETAPA_VIDA_VIAJERO] = "Joven (19 a 25 años)" then 3 else if [ETAPA_VIDA_VIAJERO] = "Adulto joven (26 a 40 años)" then 4 else if [ETAPA_VIDA_VIAJERO] = "Adulto intermedio (41 a 50 años)" then 5 else if [ETAPA_VIDA_VIAJERO] = "Adulto tardío (51 a 60 años)" then 6 else if [ETAPA_VIDA_VIAJERO] = "Senior (61 años a más)" then 7 else if [ETAPA_VIDA_VIAJERO] = "Sin data de edad del viajero" then 8 else null),
    #"Valor reemplazado" = Table.ReplaceValue(#"Columna condicional agregada1",null,"Sin data de género del viajero",Replacer.ReplaceValue,{"NOM_GENERO"})
in
    #"Valor reemplazado"

[BA_Pete]

The only thing I can see that might affect the inital enabling of query folding is the command timeout argument. Try either removing that argument from the code directly so it looks like the below or, if that doesn't work, remake the connection to the server/db but don't specify any timeout or SQL Statement.

let
    Origen = Sql.Database("000.1.8.00", "database"),
    dbo_REPORTE_VENTA_COMERCIAL = Origen{[Schema="dbo",Item="REPORTE_VENTA_COMERCIAL"]}[Data],
    ...
in
    ...

I've not checked whether any of your subsequent steps will break query folding, the main thing is to just get the #"Filas >=2023 filtradas" step folding, then you know it's working.

Pete

[BA_Pete]

Hi @AlekseiDiazPUCP ,

Ok, two things:

-1-

SQL Servers don't generally recognise quotation marks ( "Material 1" ) as a string signifier. You should try using apostrophes instead ( 'Material 1' )

-2-

Why are you using the advanced statement connection type anyway? For very simple connection requirements like this I would strongly recommend using the SQL Server connection with NO defined statement (leave the box blank) then applying these filters within Power Query. PQ will fold your filter operations back to the source via the native query generated behind the scenes anyway, so there will be no additional load on the source, but you will get the benefits of simplicity and visibility, both valuable when returning to an old project or handing over maintenance to another dev.

You can check that your query is folding to the source by right-clicking on the query steps and ensuring that "View Native Query" is not greyed-out.

Pete