Ir para conteúdo
  • 0

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


elaine.smendonca
Ir para solução Solucionado por Vitor Peralva ,

Pergunta

  • Membros

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?

Link para o comentário
Compartilhar em outros sites

7 respostass a esta questão

Posts Recomendados

  • 0
  • Membros
  • Solução
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.

image.png.76990fa898634f26f5372b1ea06e48d5.png

 

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

image.thumb.png.e6413c8779d390037a454a2a1f1d486f.png

 

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.

Link para o comentário
Compartilhar em outros sites

  • 0
  • Membros

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.

 

Link para o comentário
Compartilhar em outros sites

  • 0
  • Membros

@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

Link para o comentário
Compartilhar em outros sites

  • 0
  • Membros
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:

image.thumb.png.85de1041127dac1b0d7ae326776056ac.png

 

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:

image.png.18f7d5d9adf2232b607c31f2f9058ef8.png

 

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:

image.thumb.png.4fc2377650520563fcf838b8d45993a8.png

 

 

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.

Link para o comentário
Compartilhar em outros sites

  • 0
  • Membros

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.

Link para o comentário
Compartilhar em outros sites

Crie uma conta ou entre para comentar

Você precisar ser um membro para fazer um comentário

Criar uma conta

Crie uma nova conta em nossa comunidade. É fácil!

Crie uma nova conta

Entrar

Já tem uma conta? Faça o login.

Entrar Agora
×
×
  • Criar Novo...