Expression.Error Power Query EXCEL

Question

I have a problem with a dynamic parameter in Power Query. There's the code:

let
    Parametro = Excel.CurrentWorkbook(){[Name="Parametro"]}[Content],
    InicioExec_Valor = Parametro{0}[Valor],
    FimExec_Valor = Parametro{1}[Valor],
    Fonte = Sql.Database("DATABASE", "TABLE", [Query="select#(lf)#(lf)o.cd_controle, exe.nm_pessoa AS Executante, o.numero AS OM, #(lf)CONVERT(nvarchar(10), o.dt_abertura, 103) AS Abertura,#(lf)o.medidor Horimetro_OM,#(lf)p.nm_pessoa AS Cliente, #(lf)e.nm_equipto AS Equipamento, pat.nr_patrimonio AS Patrimonio,  #(lf)CONVERT(nvarchar(10), o.dt_autoriz_execucao, 103) AS Inicio_Exec, #(lf)CONVERT(nvarchar(10), o.dt_encos_oficina, 103) AS Fim_Exec, Z.nm_apelido AS Unidade, #(lf)CASE WHEN fl_preventiva = 'C' THEN 'Corretiva' #(lf)WHEN      fl_preventiva = 'P' then 'PREVENTIVA'#(lf)WHEN      fl_preventiva = 'R' then 'INSPEÇÃO RESUMIDA'#(lf)WHEN      fl_preventiva = 'V' then 'INSPEÇÃO PREVENTIVA'#(lf)WHEN      fl_preventiva = 'E' then 'ENTREGA TÉCNICA'#(lf)else      'Indefinido'      end AS 'Corret_Preven'#(lf),CONVERT(nvarchar(10), fl_remessa.dt_saida, 103) AS DataSaida#(lf),fl_rem_equ.vl_medidor Horimetro_Remessa#(lf),CONVERT(nvarchar(10), o.dt_abertura, 103) AS Abertura#(lf),o.medidor Horimetro_OM#(lf)#(lf)from orcos o#(lf)inner join controle c on (c.cd_controle = o.cd_controle)#(lf)inner join wcore_oid oid on (oid.cd_oid = c.cd_oid)#(lf)left outer join empresa AS Z ON Z.cd_empresa = o.cd_empresa #(lf)left outer join equipto e on (e.cd_equipto = o.cd_equipto)#(lf)left outer join pessoa f on (f.cd_pessoa = o.cd_pessoa_tec)#(lf)left outer join pessoa p on (p.cd_pessoa = o.cd_pessoa)#(lf)left outer join pessoa exe on (exe.cd_pessoa = o.cd_pessoa_exe)#(lf)left outer join patrimon pat on (pat.cd_patrimonio = o.cd_patrimonio)#(lf)left outer join est_almox x on x.cd_almox = pat.cd_almox#(lf)left outer join empresa emp on emp.cd_empresa = o.cd_empresa_origem #(lf)#(lf)left outer JOIN dbo.fich_loc ON (fich_loc.cd_controle= o.cd_controle_loc)#(lf)left outer JOIN dbo.fl_remessa fl_remessa ON (fl_remessa.cd_controle = dbo.fich_loc.cd_controle)#(lf)#(lf)INNER JOIN(select max(fl_remessa.cd_flremessa)cd_flremessa, A.cd_controle #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)              from dbo.fl_remessa#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)              inner join dbsislocsalvador..fich_loc on (fl_remessa.cd_controle = dbo.fich_loc.cd_controle)#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  inner join dbsislocsalvador..orcos a on (fich_loc.cd_controle= A.cd_controle_loc#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  and fl_remessa.dt_saida<=a.dt_abertura)   #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  inner join controle c on (c.cd_controle = A.cd_controle)#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  left outer join patrimon pat on (pat.cd_patrimonio = a.cd_patrimonio)#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  LEFT OUTER JOIN dbo.fl_rem_equ AS fl_rem_equ ON pat.cd_patrimonio = fl_rem_equ.cd_patrimonio AND fl_remessa.cd_flremessa = fl_rem_equ.cd_flremessa#(lf)                      #(tab)#(tab)      left outer  JOIN dbo.loc_flremequ_xplano AS loc_flremequ_xplano ON loc_flremequ_xplano.cd_flremequ = fl_rem_equ.cd_flremequ  #(lf)                      #(tab)#(tab)#(tab)  left outer JOIN dbo.equipto AS equipto ON fl_rem_equ.cd_equipto = equipto.cd_equipto#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  where #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  ((NOT EXISTS(select top 1 * from config_tag_xoid)) OR (c.cd_oid not in (select txo.cd_oid from #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  (select * from ( select cd_tag, nm_tag , (30) as fl_acesso from config_tag t where t.fl_ativo in ('S') ) tags #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  WHERE (fl_acesso = 10) ) tags inner join config_tag_xoid txo on tags.cd_tag = txo.cd_tag where 3=3 /*filter_tag_clause*/ #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  group by txo.cd_oid))) and ( ( (a.cd_controle_loc is not null and a.cd_fldevolucao is null) ) ) and#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  (a.cd_empresa IN (24,45,5,46,20,29,43,15,48,10,1,22,8,34,49,9,47,52,7)) and (  ( a.cd_local is null or a.cd_local in (0,1,2,3,5,6,7,8,9) ) )  #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  AND (a.cd_controle_loc IS NOT NULL) AND (a.cd_fldevolucao IS NULL) #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  and (a.fl_preventiva in ('C', 'P', 'R', 'V', 'E')) #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  and (equipto.cd_grupo in (1478, 1480, 1481, 1482, 1483, 1484, 1485, 1486, 1487, 1488, 1491, 1492, 1548, 1549))#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  group by a.cd_controle) AS fl_remessa_max #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  ON fl_remessa.cd_flremessa = fl_remessa_max.cd_flremessa#(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)  and fl_remessa_max.cd_controle = o.cd_controle #(lf)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)     #(lf)LEFT OUTER JOIN dbo.fl_rem_equ AS fl_rem_equ ON pat.cd_patrimonio = fl_rem_equ.cd_patrimonio AND fl_remessa.cd_flremessa = fl_rem_equ.cd_flremessa#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)#(tab)     #(lf)left outer JOIN dbo.equipto AS equipto ON fl_rem_equ.cd_equipto = equipto.cd_equipto#(lf)            #(tab)#(tab)#(tab)#(tab)#(tab)  #(lf)WHERE ((NOT EXISTS(select top 1 * from config_tag_xoid)) OR (c.cd_oid not in (select txo.cd_oid from #(lf)(select * from ( select cd_tag, nm_tag , (30) as fl_acesso from config_tag t where t.fl_ativo in ('S') ) tags #(lf)WHERE (fl_acesso = 10) ) tags inner join config_tag_xoid txo on tags.cd_tag = txo.cd_tag where 3=3 /*filter_tag_clause*/ #(lf)group by txo.cd_oid))) and ( ( (o.cd_controle_loc is not null and o.cd_fldevolucao is null) ) ) and#(lf)(o.cd_empresa IN (24,45,5,46,20,29,43,15,48,10,1,22,8,34,49,9,47,52,7)) and (  ( o.cd_local is null or o.cd_local in (0,1,2,3,5,6,7,8,9) ) )  #(lf)AND (o.cd_controle_loc IS NOT NULL) AND (o.cd_fldevolucao IS NULL) #(lf)and (o.fl_preventiva in ('C', 'P', 'R', 'V', 'E')) #(lf)and (equipto.cd_grupo in (1478, 1480, 1481, 1482, 1483, 1484, 1485, 1486, 1487, 1488, 1491, 1492, 1548, 1549))"]),
    #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Abertura", type date}, {"Inicio_Exec", type date}, {"Fim_Exec", type date}, {"DataSaida", type date}}),
    #"Colunas Removidas" = Table.RemoveColumns(#"Tipo Alterado",{"cd_controle", "Horimetro_OM2", "Abertura2"}),
    #"Filtro Datas" = Table.SelectRows(#"Colunas Removidas", each [Fim_Exec] >= InicioExec_Valor and [Fim_Exec] <= FimExec_Valor)
in
    #"Filtro Datas"

And occur that error:

Expression.Error: Não conseguimos aplicar o operador < aos tipos Number e Date. Detalhes: Operator=< Left=42795 Right=01/06/2007

How can I solve that?

Note: MY PARAMETER (01/03/2017) ARE FORMATED AS TEXT.

Eugene Eugene · Accepted Answer · 2017-03-27T17:20:53

Although I don't read Spanish, looks like it says you cannot compare number and date with < operator.

It is good practice, by the way, to first convert parameters to the proper type:

InicioExec_Valor = Date.From(Parametro{0}[Valor]),
FimExec_Valor = Date.From(Parametro{1}[Valor]),`

Try this.

If it won't work, determine step that generates error by clicking them one-by-one.

There is more I wonder about. Why do you convert Fim_Exec to nvarchar and then to date?
1. #(lf)CONVERT(nvarchar(10), o.dt_encos_oficina, 103) AS Fim_Exec
2. {"Fim_Exec", type date},
Why don't use Fim_Exec = CAST(o.dt_encos_oficina as date)? (or datetime, depending on SQL Server version) in the query?

Same applies to other columns.

Next, it is best practice not to use native queries unless absolutely required. And, yes, this is largest and most complex query I've seen up to date. :)

Expression.Error Power Query EXCEL

1 Answers