Contar dados diferentes de nulo de um range de colunas Power query

[elaine.smendonca]

Pessoal Boa noite!

Preciso de uma ajuda urgente para um relatório do trabalho. Vou exemplificar:

Tenho uma planilha com 30 colunas, dessas 30 colunas preciso contar em cada linha quantas vezes neste range de colunas  o valor nulo aparece. Seria mais ou menos fazer a fórmula abaixo no excel: =CONT.SE(P2:T2;"<>"). É possível e correto fazer no powerquery?

[Vitor Peralva]

7 horas atrás, elaine.smendonca disse:

Já fiz algo parecido, porém quero tornar mais dinâmico sem ter colocar todas as colunas:

List.Count(List.Select({[#"START AQUISIÇÃO (RFQ) Realizado"],[PARECER NFVI Realizado],[ADJUDICAÇÃO Realizado],[JURÍDICO Realizado],[ASSINATURA CONTRATO Realizado],[APROVAÇÃO SAP Realizado],[LIBERAÇÃO LPU Realizado],[LIBERAÇÃO ORÇAMENTÁRIA Realizado],[#"CRIAÇÃO BOQ HW/SW Realizado"],[#"CARGA BOQ HW/SW Realizado"],[#"APROVAÇÃO RC HW/SW Realizado"],[#"EMISSÃO PO HW/SW Realizado"],[#"VALIDAÇÃO PO HW/SW Realizado"],[CRIAÇÃO BOQ SERVIÇO Realizado],[CARGA BOQ SERVIÇO Realizado],[APROVAÇÃO RC SERVIÇO Realizado],[EMISSÃO PO SERVIÇO Realizado],[VALIDAÇÃO PO SERVIÇO Realizado],[CRIAÇÃO BOQ MATERIAL Realizado],[CARGA BOQ MATERIAL Realizado],[APROVAÇÃO RC MATERIAL Realizado],[EMISSÃO PO MATERIAL Realizado],[VALIDAÇÃO PO MATERIAL Realizado],[TRANSFER MEETING Realizado]}, each _ = "N/A"))

Estou tentando substituir por List.Range({[#"START AQUISIÇÃO (RFQ) Realizado"]..[VALIDAÇÃO PO MATERIAL Realizado]}, [#"START AQUISIÇÃO (RFQ) Realizado"]) mas não está funcionando.

Perfeito, se você conhece o List.Range, fica mais fácil lhe sugerir uma outra solução:

 

Use o seguinte código:

= List.NonNullCount(List.Range(Record.FieldValues(_),0,7))

Tentar explicar:

O List.Range vai criar uma lista de valores.

Que valores?

O primeiro argumento deve ser uma lista, para ele vamos passar o Record.FieldValues(_), que vai retornar o registro daquela linha.

Então se você tiver 50 colunas no seu modelo, cada "célula" daquela linha será um item da lista.

O segundo item é o offset, que seria o deslocamento.

Normalmente temos algumas colunas iniciais, como ID, Descrição, etc., estas colunas não queremos avaliar.

Desta forma, com o offset, indicamos a partir de onde queremos começar.

Problema: O Power Query começa a contagem com 0 (zero) e não um. Assim, a coluna 1 na realidade é zero, a coluna 2 na realidade é 1.

Assim, ele vai trazer 7 colunas, da Coluna 0 interna (Coluna 1) até a Coluna 6 interna (Coluna 7).

Como no arquivo que enviou estaria se contando desde a primeira coluna, então, utilizei o zero.

Veja a partir de que coluna vai começar e utilize Nº da Coluna - 1.

O terceiro argumento é opcional, se começar por exemplo na sétima coluna e for até a ultima coluna, você pode omitir ele.

Como tinha uma coluna de "quantidade de nulos" no arquivo enviado, usei ele para alertar quantas colunas quero e ignorar esta última.

 

Se você colocar somente a primeira parte da fórmula, ou seja, =List.Range(Record.FieldValues(_),0, 7), você consegue, clicando na parte branca ao lado de List, você consegue ver a lista na parte inferior, é uma forma de ir auditando a fórmula.

Beleza, temos uma lista de registros, das colunas escolhidas para cada linha.

Precisamos descobrir, agora, quantos NÃO SÃO NULOS.

Para isto utilizamos a função de lista List.NonNullCount.

Ela só tem um argumento, que é uma lista.

Basta colocar a nossa fórmula anterior entre parentêses.

 

Agora basta dar OK e dar aquela conferida se está batendo os cálculos.

 

Para o exemplo que listou, tudo bateu certinho.

Espero que agora tenha conseguido lhe ajudar e, principalmente, que tenha entendido a utilização dos argumentos.

Seu caso é interessante e vou, depois, gravar um vídeo sobre a solução.

Infelizmente, como estou acompanhando uma pessoa da família no hospital, não tenho como prometer uma data, mas tento te enviar uma mensagem com o link, para se quiser ver.

 

Procurei ser o mais didático possível, mas, se algo não ficou claro, pode se manifestar aqui que discutimos para evoluirmos juntos e, claro, outros colegas podem ajudar.

[Vitor Peralva]

Boa Noite, @elaine.smendonca!

 

Você não trouxe uma amostra de dados, bem como não deixou bem claro o objetivo.

Desta forma, vou trabalhar no campo mais teórico.

 

Vou começar com o questionamento sobre: É correto fazer no powerquery?

O Power Query pode ser utilizado tanto no Excel, quanto no Power BI.

Em ambos é possível utilizar a linguagem DAX.

Normalmente, se envolve cálculo, deve-se se fazer via DAX.

Então, o ideal seria fazer no DAX.

Mas tudo depende do objetivo.

Se precisa simplesmente fazer um relatório em Excel para imprimir, pode ser que usar o Power Query seja o suficiente.

 

Uma segunda pergunta sua: É possível fazer no powerquery?

A resposta é sim.

Talvez a maneira mais fácil para quem não tem tanto conhecimento seria o seguinte.

Vamos imaginar que a sua consulta tenha o nome de Dados.

Você cria uma Consulta Nula (Guia Página Inicial --> Nova Fonte --> Consulta Nula).

Na Barra de Fórmulas, você vai colocar

= Table.Profile(Dados)

Ele vai gerar uma consulta com análises de todas as colunas da consulta Dados.

Você vai observar que esta consulta terá 8 colunas. Para você, somente as duas a seguir importam:

Coluna 1 = Column = Nome da Coluna Original

Coluna 7 = NullCount = Contagem dos Valores Nulos na Coluna.

As demais podem ser eliminadas.

Pronto, você terá o valor de nulos para cada uma das suas colunas.

 

Espero ter ajudado.

 

[elaine.smendonca]

@Vitor Peralva obrigada pelo retorno. Para exemplificar melhor, segue modelo de planilha em anexo.

A ideia é adicionar uma coluna no power query "Diferente de nulo" com o resultado da fórmula do excel =CONT.SE(A2:G2;"<>") para cada linha.

Consegue ajudar? Para a minha necessidade preciso que seja no power query e como na base do trabalho são muitas colunas queria trabalhar com o range como fórmula do excel ao invés de colocar coluna a coluna.

modelo duvida power query.xlsx

[Vitor Peralva]

23 minutos atrás, elaine.smendonca disse:

@Vitor Peralva obrigada pelo retorno. Para exemplificar melhor, segue modelo de planilha em anexo.

A ideia é adicionar uma coluna no power query "Diferente de nulo" com o resultado da fórmula do excel =CONT.SE(A2:G2;"<>") para cada linha.

Consegue ajudar? Para a minha necessidade preciso que seja no power query e como na base do trabalho são muitas colunas queria trabalhar com o range como fórmula do excel ao invés de colocar coluna a coluna.

modelo duvida power query.xlsx 8 kB · 0 downloads

 

OK, entendi que você quer fazer o cálculo linha a linha.

A sua amostra me parece um tanto quanto incompleta, já que faltaria um ID ou algo para individualizar a linha.

Mas considerando que vai querer analisar as 7 primeiras colunas teríamos:

 

Importe o arquivo para o Power BI.

Ele vai criar algumas etapas automáticas e você terá algo como:

 

Vá na Guia Adicionar Coluna --> Coluna Personalizada.

Na janela que se apresentar, dê um nome a coluna que quer criar, por exemplo, Qtd Não Nula.

E use a seguinte formula:

= let

c1 = if [CLASSIFICAÇÃO] <> null then 1 else 0,
c2 = if [PROJETO] <> null then 1 else 0,
c3 = if [#"SUB-PROJETO/VNF"] <> null then 1 else 0,
c4 = if [VENDOR] <> null then 1 else 0,
c5 = if [HYPERVISOR] <> null then 1 else 0,
c6 = if [SITE] <> null then 1 else 0,
c7 = if [TIPO SITE] <> null then 1 else 0,
cR = c1 + c2 + c3 + c4 + c5 + c6 + c7

in 

cR

 

Sua tela ficará assim:

 

Observe que verificamos para cada coluna daquela linha se ela é nula ou não. Se não for nula, atribuímos o valor 1, se nula, o valor 0.

O número de valores não nulos será o somatório.

Ao invés de fazermos uma etapa para cada coluna, agrupamos tudo em uma única.

 

Ao dar OK, você terá o resultado:

 

 

Não é tão performático e simples como no Excel, em especial, porque não é objetivo do Power Query fazer este tipo de transformação, mas conseguimos uma forma de o fazer.

 

Espero que tenha conseguido ajudar.

[elaine.smendonca]

Oi @Vitor Peralva obrigada pelo retorno..

Como disse são muitas colunas na base que eu usarei no trabalho. Pensei em tentar algo com List.Range é possível?

[elaine.smendonca]

Já fiz algo parecido, porém quero tornar mais dinâmico sem ter colocar todas as colunas:

List.Count(List.Select({[#"START AQUISIÇÃO (RFQ) Realizado"],[PARECER NFVI Realizado],[ADJUDICAÇÃO Realizado],[JURÍDICO Realizado],[ASSINATURA CONTRATO Realizado],[APROVAÇÃO SAP Realizado],[LIBERAÇÃO LPU Realizado],[LIBERAÇÃO ORÇAMENTÁRIA Realizado],[#"CRIAÇÃO BOQ HW/SW Realizado"],[#"CARGA BOQ HW/SW Realizado"],[#"APROVAÇÃO RC HW/SW Realizado"],[#"EMISSÃO PO HW/SW Realizado"],[#"VALIDAÇÃO PO HW/SW Realizado"],[CRIAÇÃO BOQ SERVIÇO Realizado],[CARGA BOQ SERVIÇO Realizado],[APROVAÇÃO RC SERVIÇO Realizado],[EMISSÃO PO SERVIÇO Realizado],[VALIDAÇÃO PO SERVIÇO Realizado],[CRIAÇÃO BOQ MATERIAL Realizado],[CARGA BOQ MATERIAL Realizado],[APROVAÇÃO RC MATERIAL Realizado],[EMISSÃO PO MATERIAL Realizado],[VALIDAÇÃO PO MATERIAL Realizado],[TRANSFER MEETING Realizado]}, each _ = "N/A"))

Estou tentando substituir por List.Range({[#"START AQUISIÇÃO (RFQ) Realizado"]..[VALIDAÇÃO PO MATERIAL Realizado]}, [#"START AQUISIÇÃO (RFQ) Realizado"]) mas não está funcionando.

[elaine.smendonca]

@Vitor Peralva PERFEITO!!!!!

Funcionou... Muito, muito obrigada!!!!!