Tabela de dimensões em um arquivo de texto: solução Power Query

Nesta postagem do blog, abordarei o problema nº 2 do Problema da semana em andamento no , onde discutimos como classificar dados confusos em uma tabela de dimensões limpa. Esta é a segunda iteração desta nova iniciativa que estamos hospedando no LuckyTemplates. Eu sou especialmente apaixonado por esta série porque ela oferece a todos a chance de praticar mais regularmente. Você pode assistir ao vídeo completo deste tutorial na parte inferior deste blog.

Na primeira quarta-feira de cada mês, há um desafio DAX e na terceira quarta-feira, há um desafio de consulta de energia .

Esta é uma ótima oportunidade para explorar, descobrir e aprender coisas novas sobre essas linguagens que você precisa aproveitar dentro do LuckyTemplates.

Você encontrará uma categoria sobre o Problema da Semana no fórum LuckyTemplates.

O foco principal é todo sobre o processo e não tanto sobre o resultado.

Se você for bem-sucedido em dividir um problema em partes menores, poderá resolver qualquer problema com o qual se depare.

Melissa de Korte

Brian falou anteriormente sobre uma técnica chamada depuração de pato de borracha . Se você perdeu isso, não deixe de conferir o vídeo dele. Pode ajudá-lo quando você ficar preso nesses desafios.

Primeiro, vamos examinar a tarefa em questão. Temos um arquivo de texto confuso que precisamos transformar em uma tabela de dimensão adequada.

Agora, quando vejo dados como este, na maioria das vezes, trata-se de limpeza de texto, remoção de caracteres indesejados, corte, capitalização de palavras e assim por diante.

Mas também precisamos recuperar todas as linhas dessa pilha de dados e transformá-la em uma única linha para cada país. Estou chamando de dados de pilha porque todos os nomes de campo são repetidos em uma única coluna para cada país.

Tenho alguma experiência no Excel e, a meu ver, um de seus recursos mais poderosos são as tabelas dinâmicas .

Índice

• Tabelas dinâmicas no Excel
• Usando o Power Query para criar uma tabela de dimensões
• Função de Limpeza de Texto Personalizado
• Girando os dados
• Conclusão

Tabelas dinâmicas no Excel

Tabelas dinâmicas permitem visualizar segmento de dados por segmento. Dependendo do que você colocar na seção de linha, uma tabela dinâmica condensa cada ocorrência desse segmento em uma única linha.

Você pode então dividir ainda mais arrastando os campos para a seção da coluna.

Eu criei um exemplo de como isso se parece. Aqui, temos praticamente os mesmos dados que tínhamos no arquivo de texto.

E na página seguinte, criei uma tabela dinâmica vazia.

Agora, o que não mostrei é que tenho três colunas aqui em vez das duas que já mostrei.

Vou arrastar meu segmento para as linhas.

Como a Coluna 1 tinha os nomes dos campos, vou arrastá-la para a seção da coluna.

A coluna 2 tinha todos os valores de campo, então vou arrastá-la para a seção de valor.

Podemos ver aqui que as tabelas dinâmicas não podem lidar com strings de texto. Ele os conta, mas mostra que temos um único valor para cada campo.

Agora vamos dar uma olhada nesse segmento que eu criei. Vou voltar para os dados e mostrar minha coluna. Você pode ver que é apenas um número de índice, identificando cada um dos blocos separados dos dados que ainda estão empilhados uns sobre os outros.

Portanto, para mim, a transformação de chave será um pivô dos dados para recuperá-los nesse formato tabular.

No fórum, vi outras maneiras de lidar com isso. Existem outras maneiras de obter um formato tabular além de usar uma ação de pivô e elas funcionam igualmente bem. Se você estiver interessado nisso, vá ao fórum e comece a explorar.

Usando o Power Query para criar uma tabela de dimensões

Vamos examinar a consulta de energia e revisar minha solução.

Pessoalmente, acho que a interface do usuário faz um ótimo trabalho ao escrever a maior parte do código M para nós. Portanto, costumo projetar minhas consultas usando a interface do usuário o máximo que posso.

Depois que a consulta fizer o que eu precisava, entrarei no editor avançado e examinarei o código M para ver se posso modificá-lo. Vamos ver o que isso parece.

Este é o meu grupo base que é construído usando a interface do usuário.

Você pode ver que há muitas etapas nas etapas aplicadas no lado direito.

Agora, isso em si não é um problema, mas apenas observando essas etapas, você pode ver que há muitas transformações que podem ser agrupadas.

Vamos abrir o editor avançado.

Vemos que esta consulta tem 31 etapas.

Também adicionei alguns comentários a essa mesma consulta contendo as 31 etapas, mas a separei em seções.

A primeira coisa que fiz foi remover a etapa do tipo de alteração. Sugiro criar uma função personalizada para fazer todas essas etapas de transformação de texto.

Há muitos comentários aqui, mas há apenas duas coisas que quero destacar nesta postagem do blog. A primeira é a função personalizada para fins de limpeza de texto .

A segunda é a etapa principal para transformar esse tipo de dados de volta em um formato tabular adequado .

Função de Limpeza de Texto Personalizado

Vamos voltar aos estágios iniciais de construção da minha consulta, onde eu tinha todas aquelas etapas agrupadas para limpar esses textos: coluna 1 e a coluna mesclada.

Eu também adicionei uma coluna personalizada adicional. Seu único propósito é desenvolver minha função personalizada de limpeza de texto . Eu invoquei isso na coluna mesclada.

Dessa forma, não preciso escrever a função de uma só vez, mas construí-la gradualmente, uma etapa por vez, adicionando uma nova transformação após revisar os resultados da etapa anterior.

Vejamos o código M para a função de limpeza de texto.

Como você pode ver, eu tenho várias etapas. Quando eu estava construindo esta função de texto, eu ia e voltava entre a consulta para ver os resultados para ver o que construir e o que corrigir a seguir.

Com esse resultado, realizei todas as transformações que precisava. Algumas das funções M que usei aqui foram fornecidas pela interface do usuário quando construí minha consulta inicial, como o Text. Função de aparar. No entanto, as outras funções utilizadas não foram.

Se você não estiver familiarizado com elas, poderá procurar todas as funções M no guia de fórmulas M online. Este é o link onde você precisa ir.

Você encontrará uma seção dedicada a consultas e funções de energia.

Se você rolar para baixo, encontrará a seção sobre função de texto e cada seção começa com uma visão geral. Há uma lista de todas as funções de texto dentro da consulta de energia e da linguagem de fórmula M.

Se você está procurando por uma transformação específica, pode procurar aqui.

Girando os dados

A segunda parte que gostaria de destacar ao criar esta tabela de dimensões é a articulação dos próprios dados. Vamos dar uma olhada nisso também.

Comecei adicionando um arquivo index. Atualizei esse índice para segmentar adequadamente os blocos de dados. Fiz isso retornando o número do índice para cada linha em que havia o país de texto na coluna 1 e, em seguida, preenchi esse valor.

Tudo o que precisamos fazer é dinamizar os próprios dados. Na guia de transformação, você encontrará a coluna dinâmica. Com a coluna 1 selecionada, clique em coluna dinâmica.

Ele usará os valores da Coluna 1 como o novo nome da coluna. Ele também quer saber onde estão os valores para esses nomes de campo. Esses estão em nossa coluna mesclada.

Se o Excel pode lidar com valores de texto, o Power Query também pode por causa de sua configuração de opções avançadas. Tudo o que precisamos fazer é selecionar Don't Aggregate para que ele possa lidar com valores de texto.

Depois de clicar em OK, podemos ver que nossos dados foram dinamizados.

Conclusão

A imagem acima é a consulta de resultado final. Espero que você tenha gostado de como remodelamos os dados confusos no arquivo de texto fornecido e os transformamos em uma tabela de dimensões limpa que é adequada para análise.

Se você gostou desta postagem do blog, inscreva-se no canal LuckyTemplates para não perder nenhum conteúdo novo.

Espero ver todos vocês em futuros desafios do Problema da Semana.

melissa