Modelagem de dados no Power Query

Nem sempre temos o ao back-end de um banco de dados e ferramentas de consulta para modelar dados. Então, o que fazer quando não temos? Podemos usar o Power Query. Ele é integrado diretamente ao Excel. Podemos importar nossos dados nele e mesclar consultas para modelá-los conforme a necessidade. Quero analisar dados salariais e alguns dados reutilizáveis sobre CEPs obtidos de geonames.org. Primeiro vamos ar o Power Query usando a guia Dados. Vou até Dados > Obter Dados > De Arquivo > Da Pasta de Trabalho. Vou navegar até os arquivos de exercícios e, primeiro, quero importar DadosCEP e escolher CEP. Logo de cara, percebo algo estranho com meus CEPs. Eles não deveriam ter três dígitos. Vamos escolher Transformar Dados e tentar descobrir o problema. Estou vendo que o cabeçalho se chama CEP_Texto, mas o tipo foi modificado. O Power Query tenta reconhecer os tipos de dados e os cria automaticamente. Vou excluir esta etapa. Quando faço isso, vejo que todos os zeros à esquerda de CEP_Texto retornam. Quero alterar este tipo de dados para texto. Assim, estes zeros à esquerda serão mantidos. Não preciso me preocupar com isso de novo. Na verdade, não preciso mais do CEP. Posso clicar com o botão direito e removê-lo. Estes dados representam apenas os Estados Unidos. Ainda assim, é muita informação. Vamos continuar clicando em Fechar e Carregar. Isso carrega os dados dos CEPs diretamente na planilha. Observe que os zeros à esquerda foram mantidos. O Excel normalmente os descarta. Muito bem, vamos pegar o próximo conjunto de dados. Vou até Dados, Obter Dados, De Arquivo, Da Pasta de Trabalho. Vou escolher PesquisaSalarial, clicar em Importar, selecionar Pesquisa e Transformar Dados. Tenho 343 respostas de uma pesquisa. Uma das primeiras coisas que quero verificar é o CEP. Vou rolar para o lado. Primeiro vou clicar com o botão direito e mover para o início. É mais conveniente quando estiver pronta para mesclagem. Vejo ainda que o tipo de dados é 123, ou seja, número. Para mesclar ou combinar, os dados precisam ser do mesmo tipo. Então, vou alterar isto para Texto. Ok, ótimo. Vou expandir minhas consultas aqui. Então, tenho os dados de CEP e os dados de Pesquisa. Os dois conjuntos de dados estão prontos para mesclagem. Na guia Página Inicial da faixa de opções do Power Query, vou até Mesclar Consultas. Se eu escolher Mesclar Consultas, os dois conjuntos de dados serão combinados em um só. Gosto de manter meus conjuntos separados, então vou clicar no menu suspenso e escolher Mesclar consultas Como Novas. Agora tenho que selecionar as tabelas. Vou escolher CEP e, assim como uma consulta em que temos que combinar dois campos, aqui selecionamos os campos correspondentes. Agora, tenho os dados de Pesquisa na parte de cima e os dados de CEP na de baixo. As duas colunas de CEP estão selecionadas, e vejo o tipo de junção: Externa esquerda (todas a partir da primeira, correspondência a partir da segunda). Então, serão mostrados todos os dados de Pesquisa em que houver um CEP correspondente. Aqui está dizendo que há correspondência em 337 das 343 linhas. Isso significa que um CEP foi digitado errado ou está faltando um CEP. Vou clicar em OK e agora tenho a nova consulta mesclada. Agora vou clicar com o botão direito em Mesclar1, escolher Renomear e chamar de CEPsPesquisa. Ok, vou criar outra consulta porque quero analisar apenas os resultados da pesquisa com pessoas do estado do Alabama e nos 67 distritos. Vou até Mesclar Consultas, selecionar Mesclar Consultas como Novas e fazer a mesma junção. Vou escolher Pesquisa e CEP. O Power Query não mostra esquerda e direita na tela, mostra superior e inferior, mas externa funciona mesmo assim. Por isso, estou menos preocupada com esquerda ou direita e mais preocupada com externa e em usar as indicações da tela. No Power Query, a parte superior é a esquerda e a inferior é a direita. Muito bem, vou escolher Externa esquerda, selecionar os CEPs e clicar em OK. Vamos chamar esta de ResultadosPesquisaAlabama. Ok. Vou fechar as configurações de consulta e rolar para o lado. Aqui está a tabela CEP. Em uma consulta, arrastamos e soltamos ou clicamos duas vezes nos campos. Em uma consulta mesclada, basta expandir. Aqui podemos definir as informações que desejamos. Quero a cidade, o estado e o distrito. Posso clicar em OK. Como vou relatar isso em um mapa, pode ser que eu precise da latitude e longitude. Vou voltar às configurações de consulta que fechei antes e clicar no ícone de engrenagem. Vamos incluir a latitude e longitude e clicar em OK. Vou voltar para CEPsPesquisa, rolar aqui até a mesclagem e selecionar tudo, exceto CEP_Texto. Também não preciso do fuso horário nos meus relatórios. Vou clicar em OK. Observe que foi acrescentado o nome CEP. Se eu não quiser o nome do título ali, posso resolver isso. Está vendo a opção Prefixo do nome da coluna padrão? Posso excluir isso e clicar em OK. Assim, fico apenas com a cidade principal e o estado. Vamos fazer o mesmo com ResultadosPesquisaAlabama. Lembrando que isso só depende do que você quer nos relatórios. Perfeito. Ok. A última etapa é filtrar pelo estado do Alabama. Vou rolar de volta, clicar no menu suspenso ao lado do estado, selecionar Alabama e clicar em OK. Muito bem, estou pronta para carregar isso tudo na planilha. Em ResultadosPesquisaAlabama, vou até a Planilha4, e aqui indica que estou em CEPsPesquisa. Vou voltar para a Planilha5. Esta tela de Consulta e Conexões pode ser útil. Vou chamar esta de SomenteAlabama. Vou renomear a Planilha4 como Pesquisa Toda com CEP, Dados Originais e CEPs. Agora vou excluir a Planilha1. Não preciso dela. Quando volto a clicar em ResultadosPesquisaAlabama, observe que isso me leva diretamente para a planilha Alabama. Ok. Vou seguir adiante e salvar meu trabalho. A modelagem de dados ficou bem mais fácil ao longo dos anos. Modelamos dados para prepará-los para limpezas posteriores e gerar melhores relatórios, mas lembre-se: um pouco de conhecimento ajuda muito.