Tempo de leitura:
Neste artigo vou mostrar-te 2 funções obrigatórias em Power Query! As funções são Table.FillDown e Table.ReplaceValue que devem ser usadas para uniformizar dados numa tabela quando precisamos de preencher linhas em branco, com valores nulos ou substituir valores como erros.
As funções aplicam-se em cenários como o da imagem em baixo, onde temos as células em branco e com erros em determinadas colunas.
Vamos ver como podemos corrigir estes cenários no Power Query.
Passar os dados para o Power Query
Começamos por passar os dados para o Power Query, convertendo o intervalo em tabela.
Preencher colunas em branco
Já no Power Query, a primeira função que vamos usar é a função Table.FillDown que é invocada assim que usamos a Transformação nas colunas selecionadas com a opção Preencher – Baixo.
Esta função funciona para substituir uma célula em branco, com o valor nulo, pelo valor imediatamente acima da célula, até encontrar a próxima célula preenchida.
Quando escolhemos a opção Cima, o processo é invertido, preenchendo as células acima com o valor da célula em baixo.
O resultado é o esperado.
Substituir valores
O próximo passo na transformação dos nossos dados é substituir valores em colunas. Neste exemplo os valores têm erros e precisamos de os substituir.
Através do Comando Substituir Valores presente no menu Transformar, podemos substituir um valor em várias colunas.
O resultado é o esperado…
Vamos analisar as fórmulas
Preencher Valores
Para preencher valores o Power Query usa a função Table.FillDown
A função devolve uma tabela com os valores nulos existentes na coluna ou colunas, preenchidos com o valor acima deles. A função necessita de uma Tabela, e a indicação das colunas a preencher em forma de Lista -> {}
= Table.FillDown(
TipoAlterado,
{“Segmento Mercado”, “País”, “Produto”, “Banda de Desconto”}
)
Substituir Valores
Para substituir valores o Power Query usa a função Table.ReplaceValue
A função devolve uma tabela onde substitui um valor por um novo valor nas colunas especificadas.
A função necessita de uma Tabela (1), o valor antigo (2), o novo valor (3), um argumento que é uma função para substituir -> função replacer (3), que basicamente pode ser texto ou valor, e a indicação das colunas a preencher em forma de Lista -> {}.
= Table.ReplaceValue(
PreenchidoParaBaixo,
“#N/A”,
0,
Replacer.ReplaceValue,
{“Vendas brutas”, “Desconto”, “Vendas”, “Custos Globais”, “Margem”}
)
O que conseguimos identificar aqui de comum nas duas funções?
As colunas sobre a forma de uma lista!!!
Sempre que temos esta lógica, o código é estático e propenso a erros! Por isso vamos utilizar uma função, Table.ColumnNames para obter de forma dinâmica os nomes das colunas necessárias para realizar cada operação.
A função Table.ColumnNames devolve exatamente o que precisamos – uma lista de valores, com os nomes das colunas que pode ser usada nas funções.
No exemplo vou criar 2 listas, uma para cada transformação
- ListaFill – Usada para a transformação FillDown
- ListaReplace – Usada para a transformação ReplaceValue
Contudo esta questão é um preciosismo, uma vez que para o cenário podemos usar todas as colunas da tabela sem afetar o resultado.
Lista FillDown
No cenário iniciei um novo passo após o Passo TipoAlterado, antes de começar as transformações.
Neste passo apliquei a função Table.ColumnNames, que devolve logo uma lista com todas as colunas. A lista pode ser reduzida para as colunas necessárias com a função List.FirstN.
= List.FirstN(
Table.ColumnNames(TipoAlterado),
4
)
A mesma lógica pode ser aplicada para a ListaReplace, mas aqui podemos usar a função List.LastN, escolhendo os últimos valores, ou mesmo a função List.Skip que salta os 4 primeiros valores.
= List.Skip(
Table.ColumnNames(TipoAlterado),
4
)
Alterar as fórmulas originais
Para concluir o processo voltamos às fórmulas originais e substituímos as listas pelas novas listas!
Preencher
Substituir
Próximo artigo:
Artigo Anterior:
Artigos por Categoria
Artigos por Categoria
- Microsoft Excel (45)
- Power Apps (16)
- Power Automate (3)
- Power BI (11)
- Power Query (14)
- Python (3)
- VBA (7)
Leave a Reply