Sumário executivo

O que é Get & Transform?

• Get & Transform é uma ferramenta de transformação de dados para uso nos pacotes de software Microsoft Excel e Power BI.
• Os dados geralmente chegam em formatos não estruturados, o que torna o processo ETL (extrair, transformar e carregar) um processo tedioso de soluções alternativas manuais.
• Get & Transform automatiza e agiliza o processo de limpeza e organização de tais dados brutos, o que auxilia na tarefa analítica de descobrir observações e tendências.
• Alguns exemplos de funcionalidade fornecidos por Get & Transform incluem: Remover colunas, agrupar dados, dividir strings em substrings e anexar linhas de outra tabela.
• Para manter fluxos de trabalho dentro do universo Excel, Get & Transform é uma excelente ferramenta que pode ser facilmente explicada e demonstrada para as partes interessadas relevantes.

Como posso usar Get & Transform?

• O acesso no Excel é através do Obter e transformar dados seção dentro do Dados aba. No Power BI ele existe no Dados Externos seção do Casa aba.
• Carregando CSVs: Importar um CSV via Get & Transform permite que ele seja limpo e tornado 'mais estreito' ou 'mais largo' para ajudar na rotação de dados. Essas instruções podem ser salvas e repetidas para futuras importações.
• Manuseio de strings de texto: como uma melhoria significativa em relação ao Texto para colunas funcionalidade no Excel, Get & Transform pode analisar rapidamente e separar texto combinado e cadeias de números em colunas separadas.
• Fontes de dados diferentes: com uma ampla variedade de arquivos de entrada aceitos, é possível trabalhar com fontes distintas, mantendo a qualidade de saída consistente e normalizada.
• Personalização com código: A linguagem M é o código funcional usado em Get & Transform, e é possível escrever consultas personalizadas para solicitações mais personalizadas.

Nesta idade de data lakes e bancos de dados em escala de petabyte, é 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 análise de dados ainda é um processo manual de localização, compilação e organização de diferentes tipos de dados.

Para o analista financeiro, os dados geralmente chegam como uma planilha do Excel, mas com a mesma frequência, são despejados 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 têm todos os componentes necessários para análise. O tempo gasto eliminando esses dados é um tempo valioso perdido para o analista, mas às vezes essa tarefa é aceita como um mal necessário a ser tolerado.

O que Get & Transform faz?

Uma solução para este problema comum é na verdade bastante acessível: Excel e Power BI têm um conjunto completo de ferramentas de transformação de dados que poucos usuários conhecem, denominado Obter e transformar (anteriormente conhecido como Power Query). O uso de sua funcionalidade integrada de extração, transformação e carregamento (ETL) permite que os analistas financeiros se conectem perfeitamente às suas fontes de dados e obtenham insights mais rapidamente.

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

• Removendo colunas,
• Filtrando os dados,
• Agrupando os dados,
• Dinamizar / remover dinamização dos dados,
• Dividindo strings em substrings,
• Extraindo palavras-chave de strings,
• Anexar linhas de outra tabela e
• Unindo duas tabelas de dimensão.

No diagrama abaixo, vemos que Get & Transform desempenha 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
• Converter arquivos de contabilidade exportados em um layout digerível
• Carregando milhões de linhas de vendas diretamente no Power Pivot
• Agrupando dados diários em resultados mensais gerenciáveis ​​antes de importá-los para o Excel
• Unindo dados de outra tabela unindo em colunas correspondentes

Geralmente, quando recebo novos dados, irei explorá-los usando Get & Transform antes de carregá-los no Power Pivot. Isso me permite ver quais transformações podem ser necessárias e executar rapidamente alguns pivôs e agrupamentos nos dados para formular uma estrutura para análise. Em muitos casos, neste estágio, descobrirei que preciso de mais dados ou que há problemas de dados. Usando uma plataforma baseada no 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 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,

Então, vou usar Get & Transform apenas para a exploração inicial de dados e, em seguida, mover o trabalho pesado para R .

Como acessar 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 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 do Dados guia e, em seguida, o Obter e transformar dados seção.

melhor maneira de aprender c

No Power BI, a funcionalidade existe no Casa guia, no Dados Externos seção.

Neste artigo, meus exemplos ocorrem 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 ajudar neste 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 despejo de dados.

Como um 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 que representam vários armazenamentos. De preferência, 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 as vantagens dos recursos de relacionamento possíveis nesses aplicativos. Veremos isso mais adiante na discussão.

adicione um listener de evento de clique no botão. passe uma função vazia como o segundo parâmetro.

Por enquanto, vamos supor que precisamos ver os dados como uma estrutura 'mais estreita e mais alta', em vez de uma 'mais larga e mais curta'. A primeira etapa é carregar o CSV; então, começaremos a “desmembrar” 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, ao clicarmos em diferentes ações, a ferramenta do lado direito está gerando uma lista de etapas aplicadas para construir a consulta. É importante entender que isso está acontecendo em segundo plano, pois será revisitado posteriormente.

Get & Transform parece e se comporta de forma semelhante entre o Power BI e o Excel na maior parte. Porém, no Excel, após clicar Fechar e carregar , há um prompt adicional. Na figura abaixo, podemos alternar entre querer ou não carregar os dados em:

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

Além disso, também temos a opção de escolher ou não Adicione esses dados ao modelo de dados . Marcar esta caixa carrega os dados em um Power Pivot mesa. Se vamos analisar os dados no Power Pivot, aconselho escolher Apenas crie uma conexão e então ter certeza de que Adicione esses dados ao modelo de dados opção é selecionada. Se os dados estiverem dentro do limite da linha do Excel e preferirmos realizar nossa análise no Excel, basta escolher Mesa .

No próximo clipe, veremos que a razão pela qual formatamos os dados para serem longos e finos é para que possamos analisar as vendas não só 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 abaixo 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 fortemente aplicado a qualquer caso em que tenhamos agrupamentos dinâmicos de dados, como:

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

Embora este artigo trate de CSV e outros arquivos Excel, Get & Transform aborda uma ampla gama de tipos de dados. Depois que uma consulta é criada, ela pode ser atualizada com o tempo, conforme os dados mudam.

2. Tratamento de strings de texto

A fim de demonstrar a capacidade de Get & Transform de manipular strings, criei outro conjunto de dados que imita um arquivo de texto que mostra 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 facilmente 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 “Tipo 1 alterado”. Visto que, em última análise, 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.

qual é a teoria da gestalt

Por que seguiríamos todas essas etapas para mapear alguns números de conta? Um verdadeiro livro-razão pode ter centenas ou até milhares de contas. Essa consulta de mapeamento rápida, como mostramos, seria escalada para esse nível sem nenhum trabalho adicional.

3. Trabalho com diferentes fontes de dados

Get & Transform suporta muitas fontes de dados diferentes. Embora não seja uma lista completa, abaixo estão alguns exemplos:

Arquivo de texto Excel Facebook Adobe Analytics Google Analytics Força de vendas Azure Redshift Faísca servidor SQL SAP HANA Teradata Google BigQuery

Pessoalmente, tentei apenas cerca de metade das conexões da lista acima. Cada um dos conectores que usei é bastante robusto; Passei de dados brutos a percepções sem uma quantidade de trabalho onerosa. 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 é gerado automaticamente para casos de uso básicos. No entanto, para uma disputa de dados mais complicada, 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 ao estágio mesas temporárias , ou para realizar tarefas mais complicadas junta-se .

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 para vários usuários, especialmente se você usar várias fontes de dados e associações. Nesses casos, sempre usarei R para implantar a troca de dados duplicáveis. Finalmente, 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 está mais confortável com. O Excel ainda é a ferramenta mais importante no arsenal de um analista financeiro. Ao incorporar a funcionalidade Get & Transform, o Excel e o Power BI se tornam ainda mais poderosos por meio da variedade de fontes de dados que podem aceitar.

Compreender o básico

Qual é o conceito de ETL?

Extrair, transformar e carregar é o processo de mover dados de diferentes fontes para um data warehouse centralizado.

Para que é usado o Power BI?

O Power BI é um software de análise de negócios da Microsoft. Ele fornece recursos profundos para visualizar dados e criar relatórios e painéis automatizados.

Para que serve um data lake?

Um data lake é um repositório único para todas as fontes de dados em uma organização. Isso pode incluir bolsões de dados estruturados e não estruturados, que podem ser utilizados e processados ​​sempre que necessário.