Explorando a funcionalidade Get & Transform do Excel

Sumário executivo

Nesta era de data lakes e bancos de dados em escala de petabytes, é chocante a frequência com que ainda recebo dados na forma de arquivos CSV, texto e Excel. Embora a análise moderna se concentre em avanços de ponta em algoritmos de aprendizado de máquina, o trabalho árduo do dia-a-dia da análise de dados ainda é um processo manual de encontrar, compilar e organizar tipos de dados díspares.

Para o analista financeiro, os dados geralmente chegam como uma planilha do Excel, mas com a mesma frequência são um despejo de dados em um CSV ou uma consulta em um banco de dados SQL. Às vezes, os dados são organizados em um layout confuso ou não possuem todos os componentes necessários para análise. O tempo gasto na limpeza desses dados é um tempo valioso desperdiçado para o analista, mas às vezes essa tarefa é aceita como um mal necessário a ser tolerado.

O que o Get & Transform faz?

Uma solução para esse problema comum é bastante acessível: o Excel e o Power BI possuem um conjunto completo de ferramentas de transformação de dados que poucos usuários conhecem, chamado Get & Transform (anteriormente conhecido como Power Query). O uso de sua funcionalidade integrada de extração, transformação e carregamento (ETL) permite que os analistas financeiros vinculem perfeitamente suas fontes de dados e obtenham insights mais rapidamente.

À medida que preparamos os dados para carregar no Excel ou no Power BI, geralmente precisamos realizar algumas transformações nos dados. Alguns exemplos de manipulação de dados incluem:

• Remoção de colunas,
• Filtrando os dados,
• Agrupando os dados,
• Girando/desarticulando os dados,
• Dividindo strings em substrings,
• Extraindo palavras-chave de strings,
• Anexando linhas de outra tabela e
• Unindo duas tabelas de dimensão.

No diagrama abaixo, vemos que Get & Transform executa essa função tediosa de pré-processar os dados antes de serem carregados.

Por que você deve usar Get & Transform?

Por que vale a pena aprender a usar Get & Transform? Bem, quando olho para o que usei pessoalmente essa funcionalidade, ela me ofereceu um conjunto maleável de ferramentas para:

• Carregando uma pasta inteira de arquivos de texto em uma única tabela de dados
• Convertendo arquivos de contabilidade exportados em um layout digerível
• Carregando milhões de linhas de vendas diretamente no Power Pivot
• Agrupar dados diários em resultados mensais gerenciáveis ​​antes de importá-los para o Excel
• Juntando dados de outra tabela juntando colunas correspondentes

Geralmente, quando recebo novos dados, vou explorá-los usando Get & Transform antes de carregá-los no Power Pivot. Isso me permite ver quais transformações podem ser necessárias e realizar rapidamente alguns pivôs e agrupamentos nos dados para formular uma estrutura para análise. Em muitos casos, nesta fase, descobrirei que preciso de mais dados ou que há problemas de dados. Ao usar uma plataforma baseada em Excel, posso iterar rapidamente com minha fonte de dados para encontrar essas anomalias de dados.

Em última análise, a decisão de permanecer no Excel ou mover a análise de dados para outra plataforma dependerá do público e da repetibilidade e distribuição da análise. Se meus clientes usarem apenas o Excel, quase sempre usarei Get & Transform para carregar os dados, Power Pivot para realizar a análise e Excel para produzir as tabelas dinâmicas e gráficos. Para o cliente, isso parecerá perfeito, pois tudo está alojado no Excel.

No entanto, se meu cliente:

1. Quer usar outra ferramenta de visualização,
2. Tem vários usuários que atualizarão os dados ou
3. Precisa empregar modelos de aprendizado de máquina,

Em seguida, usarei Get & Transform apenas para a exploração inicial de dados e, em seguida, passarei o trabalho pesado para o R.

Como acessar o Get & Transform no Excel ou Power BI

Nas versões anteriores do Excel, o Power Query era um suplemento que podia ser instalado para ajudar nas funções de ETL. No entanto, no Excel 2016 e no Power BI, essas ferramentas são mais integradas. No Excel 2016, eles podem ser acessados ​​por meio da guia Dados e, em seguida, da seção Obter e Transformar Dados .

No Power BI, a funcionalidade existe na guia Página Inicial , na seção Dados Externos .

Neste artigo, meus exemplos acontecem no Power BI, mas a interface é quase idêntica à do Excel. Vou apontar as diferenças quando elas surgirem, então o tutorial deve fazer sentido para ambos os tipos de usuários.

1. Carregando arquivos CSV

Para auxiliar este tutorial, criei alguns exemplos de dados de vendas para um varejista fictício que vende roupas e equipamentos para atividades ao ar livre. Em cada um desses exemplos, os dados serão produzidos de maneiras diferentes para demonstrar métodos realistas de despejos de dados.

Como exemplo inicial, veremos os dados apresentados como um grande despejo de dados em um arquivo CSV. O fator complicador é que os dados são apresentados com várias colunas representando várias lojas. Idealmente, gostaríamos de importar e transformar os dados em um layout mais utilizável.

Abaixo está uma captura de tela da aparência do CSV bruto:

Por que queremos mudar isso? Para aproveitar os recursos de relacionamento que são possíveis nesses aplicativos. Veremos isso mais adiante na discussão.

Por enquanto, vamos supor que precisamos ver os dados como uma estrutura “mais estreita e mais alta”, em vez de uma estrutura “mais larga e mais curta”. O primeiro passo é carregar o CSV; então, começaremos a “despivotar” os dados.

Como você pode ver, a estrutura final dos dados é mais estreita do que os dados iniciais e muito mais longa. Outro ponto é que, conforme vamos clicando em ações diferentes, a ferramenta do lado direito está gerando uma lista de passos aplicados para construir a consulta. É importante entender que isso está acontecendo em segundo plano, como será revisitado mais tarde.

O Get & Transform parece e se comporta de maneira semelhante entre o Power BI e o Excel na maioria das vezes. No entanto, no Excel, após clicar em Fechar e carregar , há um prompt adicional. Na figura abaixo, podemos alternar entre se desejamos carregar os dados em:

1. Uma tabela no Excel,
2. Uma tabela dinâmica criada em relação aos dados,
3. Um gráfico dinâmico criado em relação aos dados ou
4. “Apenas crie uma conexão.”

Além disso, também temos a opção de adicionar ou não esses dados ao modelo de dados . Marcar esta caixa carrega os dados em uma tabela do Power Pivot. Se formos analisar os dados no Power Pivot, aconselho escolher Only Create a Connection e, em seguida, certificar-se de que a opção Add this data to the Data Model esteja selecionada. Se os dados estiverem dentro do limite de linhas do Excel e preferirmos realizar nossa análise no Excel, basta escolher Tabela .

No próximo clipe, veremos que a razão pela qual formatamos os dados para serem longos e finos é para podermos analisar as vendas não apenas por loja, mas também por região e estado. Para realizar essa tarefa, importaremos uma tabela que mapeia cada loja para uma região e estado. Veremos a seguir que podemos criar rapidamente relatórios que mostram as vendas por esses diferentes agrupamentos.

Você pode imaginar como esse tipo de recurso de transformação de dados no Excel, ou Power BI, pode ser aplicado de maneira poderosa a qualquer caso em que tenhamos agrupamentos dinâmicos de dados, como:

• Acumulando dados diários em semanas, meses e trimestres;
• Agrupar o pessoal de vendas em departamentos e regiões; ou
• Mapeamento de SKUs para tipos de produtos.

Embora este artigo aborde arquivos CSV e outros arquivos do Excel, Get & Transform aborda uma ampla variedade de tipos de dados. Depois que uma consulta é criada, ela pode ser atualizada ao longo do tempo à medida que os dados mudam.

2. Manipulando Strings de Texto

Para demonstrar a capacidade do Get & Transform de manipular strings, criei outro conjunto de dados que imita um arquivo de texto mostrando as transações contábeis do razão geral (GL) de uma empresa.

Observe como o número da conta e o nome aparecem na mesma string? No Power BI, podemos analisar sem esforço o número e o nome da conta em campos separados.

Neste vídeo, você pode ver que depois de dividir a coluna, a ferramenta adivinhou que o novo lado esquerdo do campo Conta deveria ser um número e cria uma etapa “Changed Type1”. Como queremos esse campo como uma string, podemos prosseguir e excluir a etapa manualmente nas etapas aplicadas.

Em seguida, pegamos os mesmos dados e criamos um plano de contas com mapeamentos para categorias de contas.

Por que passaríamos por todas essas etapas para mapear alguns números de conta? Um livro-razão real pode ser centenas ou mesmo milhares de contas. Essa consulta de mapeamento rápido, como mostramos, seria dimensionada para esse nível sem trabalho adicional.

3. Trabalhando com diferentes fontes de dados

O Get & Transform oferece suporte a muitas fontes de dados diferentes. Embora não seja uma lista exaustiva, seguem alguns exemplos:

Pessoalmente, tentei apenas cerca de metade das conexões da lista acima. Cada um dos conectores que usei foi bastante robusto; Passei de dados brutos a insights sem muito trabalho. Igualmente importante, ele serve como um validador entre diferentes fontes de dados, garantindo que as saídas finais tenham um nível normalizado de controle de qualidade.

4. Personalizando o código com a linguagem M

Em segundo plano, Get & Transform está gerando código cada vez que clicamos em um botão na ferramenta ou fazemos uma seleção. Abaixo está um exemplo de como você acessaria o código para a consulta de mapeamento de conta que criamos:

O código usa uma linguagem funcional chamada M, que gera automaticamente para casos de uso básicos. No entanto, para disputas de dados mais complicadas, podemos editar e escrever nosso próprio código. Na maioria dos casos, farei apenas pequenas modificações neste código. Em transformações mais complicadas, posso escrever a maior parte do código do zero para preparar tabelas temporárias ou para realizar junções mais complicadas.

Os limites de obter e transformar

O Excel tende a atingir seus limites quando você tenta exportar mais de um milhão de linhas. Nos casos em que transformei milhões de linhas com Get & Transform, a única maneira de enviar linhas desagrupadas é por meio de hacks tediosos ou soluções alternativas. Também descobri que as consultas Get & Transform podem ser instáveis ​​para implantar em vários usuários, especialmente se você usar várias fontes de dados e junções. Nesses casos, sempre usarei o R para implantar a disputa de dados duplicáveis. Por fim, o Excel não foi desenvolvido para modelagem de dados mais avançada. Você pode realizar regressões lineares muito rapidamente, mas além disso, você precisará usar uma plataforma mais rigorosa.

Dito tudo isso, acho que o Excel é o que a maioria dos meus clientes se sente mais confortável. O Excel ainda é a ferramenta mais importante no arsenal de um analista financeiro. Ao incorporar a funcionalidade Obter e Transformar, o Excel e o Power BI se tornam ainda mais poderosos por meio da variedade de fontes de dados que podem aceitar.