Forum Discussion

AlekseiDiazPUCP's avatar
AlekseiDiazPUCP
Regular Visitor
3 years ago

Using SQL Statement in advance option for sql databse connector

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"""])

 

Need Help

5 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

    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

  • AlekseiDiazPUCP's avatar
    AlekseiDiazPUCP
    Regular Visitor
    3 years ago

    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's avatar
      BA_Pete
      Super User
      3 years ago

       

      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's avatar
        AlekseiDiazPUCP
        Regular Visitor
        3 years ago

        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"