Você já quis consolidar, combinar ou anexar dados de várias planilhas ou pastas de trabalho do Excel ou arquivo cvs? Aposto que sim, e tem sido um processo doloroso ser feitas manualmente ou através de 3 rd party add-ins. Neste artigo, mostraremos como, com a versão mais recente do Power BI Desktop e do Power Query para Excel, você pode combinar vários dados de arquivos do Excel em uma grande tabela.

O cenário

Neste caso específico, temos um grupo do Office 365 que criamos, onde armazenamos alguns dados de vendas que não vêm de nosso sistema. Esses arquivos são fornecidos por um terceiro para nos dar uma melhor compreensão de todo o mercado e como alguns produtos estão saindo em cada mercado.

Aqui estão algumas observações sobre nosso caso:

Cada um desses arquivos tem apenas 1 mês de dados - teremos um arquivo para janeiro com apenas 1 folha que terá todos os dados de janeiro.

Todos esses são arquivos do tipo csv.

Todos os arquivos têm a mesma estrutura, mas nomes de planilhas diferentes - todos os arquivos têm apenas 1 planilha e todos eles têm os mesmos cabeçalhos, mas em um cenário de caso real, o desktop do Power BI e o processo descrito aqui funcionarão se você não tem os mesmos cabeçalhos em todos os arquivos.

Agora, o que queremos fazer é algo que pode ser resumido em uma série de etapas como a seguinte:

1. Aceder todos os arquivos do nosso site - precisamos nos conectar ao nosso Office 365 ou site do SharePoint e obter os arquivos primeiro.

2. Combinar todos os dados dos arquivos em uma grande tabela - assim que nos conectarmos a esta fonte de dados, precisamos ser capazes de combinar / consolidar / anexar todos os dados de cada um desses arquivos csv em uma grande tabela.

3. Limpar e transformar os dados - em última análise, queremos remover a dinâmica de algumas colunas e também tornar a tabela mais “gerenciável” para uso com o Power BI Desktop e a linguagem DAX.

No final, queremos que o resultado da combinação seja semelhante a este:

Obtendo dados do SharePoint

Em primeiro lugar, precisamos obter os dados e o Power BI Desktop torna mais fácil para nós obter nossos dados do SharePoint ou de qualquer armazenamento do Office 365.

Na experiência Obter dados , vamos encontrar a opção “Pasta do SharePoint”. Esta opção exigirá apenas uma string de texto que seria a URL de um site do SharePoint.

Onde posso encontrar esse chamado URL? Qual dos URLs encontrados no SharePoint devo usar ??

Boa pergunta! O que a ferramenta espera aqui é a URL raiz do próprio site do SharePoint. Aqui está um exemplo rápido:

Meu URL raiz do site do SharePoint é https://XXXXXXXXX.sharepoint.com/, mas todos os meus arquivos estão, na verdade, em outro site que começa com URL: https://XXXXXXXXXX.sharepoint.com/sites/<NOME DO SITE>/

Para usar a URL correta, precisamos estar na pasta dos dados que estamos tentando obter e verificar a url que nosso navegador mostra. Se tiver o se começar com o formato https: // <endereço do site> / sites / <nome do site> /, precisamos usar esse url, caso contrário, usaremos o https: // <endereço do site> muito mais simples

No meu caso, usarei o URL https://XXXXXXXXXX.sharepoint.com/sites/ para me conectar ao meu site.

Depois de inserir o url, suas credenciais serão solicitadas - certifique-se de selecionar o tipo “Organizacional” para suas credenciais e insira as mesmas credenciais que você usa para seu site do SharePoint.

Agora que entramos, poderemos ver todos os arquivos de todas as pastas às quais temos acesso naquele site específico . Esta pode ser uma lista bem longa, então vamos fazer alguma filtragem na coluna “Caminho da pasta” e selecionar a pasta onde seus dados estão sendo armazenados para que você obtenha apenas os arquivos dela.

A filtragem é fundamental para que você sempre obtenha os arquivos da pasta que deseja. Você também pode selecionar várias pastas se desejar.

Agora temos todos os arquivos que queremos combinar, mas como combinamos esses arquivos ?? Que tipo de magia é necessária para consolidar todos eles? Certamente não pode ser apenas um clique para combiná-los, certo? Vamos descobrir.

Funcionalidade Extra! Combine arquivos Excel ou CSV com apenas 1 clique

Você deve se lembrar dessas postagens populares da própria Microsoft sobre como combinar arquivos de texto e CSV. Ele costumava gerar resultados estranhos com arquivos do Excel, mas isso mudou com a versão mais recente do Power BI Desktop e do Power Query.

Isso agora é o que acontece depois que você clica nesse botão:

1. Uma nova janela aparecerá chamada 'Combinar Ficheiros' - ela encontrará primeiro o primeiro binário da tabela, que em nosso caso é Jan-2020.csv e criará uma função de amostra que será aplicada a todos os outros binários.

2. O Power Query / Power BI Desktop fará todo o trabalho pesado para você - você acabará com uma tabela como a que vemos na próxima imagem, onde poderá ver todos os objetos de cada arquivo. No nosso caso, vemos na coluna “Source.Name” que todos os nossos objetos são simplesmente folhas da pasta de trabalho e na coluna Nome vemos o nome da pasta de trabalho de onde vem cada objeto

Após apenas 1 clique, você tem todos os dados prontos para serem combinados na coluna “Dados”. Os elementos dentro do contorno vermelho e laranja foram criados automaticamente pela ferramenta.

Neste ponto, podemos verificar se nossos dados foram consolidados em uma tabela

SIM!!!! Todos os dados de todos os 12 arquivos CSV foram anexados e agora temos uma tabela com todos esses dados.

Mais algumas etapas de limpeza e transformação

Depois que tudo foi consolidado, avançamos e executamos algumas limpezas e transformações mostradas dentro da área laranja destacada
Aqui está uma lista rápida das etapas finais que realizamos:

• Coluna removida - removemos uma coluna desnecessária: “Source.Name”.

• Substituir Valores – Substituímos o ponto pela vírgula, para assim conseguirmos transformar a coluna ‘Margem’ de texto para numérica.

• Tipo alterado - definimos os tipos de dados para cada uma das colunas (de texto para o tipo numérico ou data) e clicamos no friso Base > Fechar e Aplicar.

O resultado

Por último, mas não menos importante, seja criativo e crie um relatório com base em seus dados consolidados!

Claro, você pode integrar essa nova tabela a um modelo de dados existente para adicionar ainda mais contexto ao seu desempenho de vendas e entender como você está se saindo em cada mercado.

Até a próxima.