Parametrizando a cláusula IN na consulta SQL do PowerQuery

Question

Eu vi exemplos ( e codificou um) que parametriza uma instrução SQL do PowerQuery com valores lidos de células na tabela da planilha. Mas eles sempre lidam com condicionais lógicos simples, como myField = <parameter> . Eu quero parametrizar uma cláusula IN ( myField IN <values> ), onde um número arbitrário de values é lido de uma tabela do Excel (também no mesmo arquivo). Alguém sabe como fazer isso?

    

Answer 1

feio, mas parece funcionar.

1. crie uma consulta PQ para capturar a tabela 'parameterValue' de coluna única com os valores de parâmetros desejados
2. adicione uma coluna fictícia e agrupe todas as linhas por manequim
3. use o Text.Combine para criar uma lista de parâmetros e remover outras colunas
4. detalhar o valor da célula
5. crie a consulta sql desejada sem a cláusula IN (use 'select top 1 ...' para eficiência)
6. usando o Editor avançado, modifique a consulta SQL delete 'top 1' e aceite a consulta da etapa 1 como parâmetro IN

query1:

let
    source = *yourData*,
    addDummy = Table.AddColumn(source, "Custom", each 1),
    group = Table.Group(addDummy, {"Custom"}, {{"Count", each _, type table}}),
    string = Table.AddColumn(group, "queryString", each Text.Combine([Count][parameterValue],"','")),
    removeOtherColumns = Table.SelectColumns(string,{"queryString"}),
    queryString = removeOtherColumns{0}[queryString]
in
    queryString

então

query2:

let
    Source = Sql.Database("*serverName*", "*dbName*", [Query=
Text.Replace("
select * from x
where parameterValue in('" & query1 & ")", ")", "')" )
])
in 
Source

    

Answer 2

Ken Puls escreveu um artigo sobre o uso de uma tabela de parâmetros com o Power Query em seu blog: link

Resumo executivo: Crie uma tabela para os parâmetros, escreva uma função para ler um parâmetro especificado, use a função no código M para aplicar dinamicamente o parâmetro desejado.

Você pode precisar estender essa abordagem para criar sua instrução SQL como uma string.

    

Answer 3

Primeiramente, eu criaria uma consulta de energia, por exemplo "Valores" para ler nas linhas da tabela do Excel. Eu configuraria o Load To = Connection Only.

Em seguida, na consulta principal baseada em SQL eu mesclaria com essa nova consulta "Values" e definiria o tipo de junção = Inner.