Tutorial: Formatar e combinar dados no Power BI Desktop

  • Artigo

Com o Power BI Desktop, você pode se conectar a muitos tipos diferentes de fontes de dados e, em seguida, formatar esses dados de acordo com suas necessidades, possibilitando a criação de relatórios visuais para compartilhar com outras pessoas. Formatar dados significa transformá-los: renomear colunas ou tabelas, converter texto em números, remover linhas, definir a primeira linha como títulos etc. Combinar dados significa conectar-se a duas ou mais fontes de dados, formatá-las conforme necessário e, em seguida, consolidá-las em uma consulta única.

Neste tutorial, você aprenderá como:

  • Formatar dados usando o Editor do Power Query.
  • Conectar-se a diferentes fontes de dados.
  • Combinar essas fontes de dados e criar um modelo de dados a ser usado em relatórios.

O Editor do Power Query no Power BI Desktop usa os menus acionados por clique com o botão direito do mouse e a faixa de opções Transformar. A maioria das opções que você pode selecionar na faixa de opções também está disponível com um clique do botão direito do mouse em um item, como uma coluna, e com a seleção de uma opção no menu que é exibido.

Formatar dados

Para formatar dados no Editor do Power Query, você fornece instruções detalhadas para que o Editor do Power Query ajuste os dados conforme eles são carregados e apresentados. A fonte de dados original não é afetada; apenas essa exibição específica dos dados é ajustada ou formatada.

O Editor do Power Query registra as etapas que você especificar (como renomear uma tabela, transformar um tipo de dados ou excluir uma coluna). Toda vez que essa consulta se conecta à fonte de dados, o Editor do Power Query executa essas etapas para que os dados sempre sejam formatados da maneira que você especificar. Esse processo ocorre sempre que você usa o Editor do Power Query ou para qualquer pessoa que usa a sua consulta compartilhada, como no serviço do Power BI. Essas etapas são capturadas sequencialmente no painel Configurações de Consulta, em ETAPAS APLICADAS. Veremos cada uma dessas etapas neste artigo.

Captura de tela de Editor do Power Query com o painel Configurações de Consulta e a lista de etapas aplicadas.

  1. Importe os dados de uma fonte da Web. Selecione o menu suspenso Obter dados e escolha Web.

    Captura de tela de Editor do Power Query com o menu Obter dados e a fonte da Web selecionados.

  2. Cole essa URL na caixa de diálogo Da Web e selecione OK.

    https://www.fool.com/research/best-states-to-retire

    Captura de tela da caixa de diálogo Da Web do Editor do Power Query com a URL da página de origem inserida.

  3. Na caixa de diálogo Navegador, selecione a caixa de seleção da entrada que inicia com Individual factor scores e escolha Transformar dados.

    Captura de tela da caixa de diálogo Navegador do Editor do Power Query com a Tabela HTML 1 selecionada e o botão Transformar Dados realçado.

    Dica

    Algumas informações nas tabelas da URL anterior podem ser alteradas ou atualizadas ocasionalmente. Como resultado, talvez seja necessário ajustar as seleções ou etapas neste artigo adequadamente.

  4. A janela do Editor do Power Query é exibida. Você pode ver as etapas padrão aplicadas até agora, no painel Configurações de Consulta em ETAPAS APLICADAS.

    • Fonte: conexão ao site.
    • Tabela Extraída do HTML: seleção da tabela.
    • Cabeçalhos promovidos: alterando a linha superior de dados em cabeçalhos de coluna.
    • Tipo alterado: alterando os tipos de coluna, que são importados como texto, para seus tipos inferidos.

    Captura de tela da janela Editor do Power Query com as Configurações de Consulta realçadas.

  5. Altere o nome da tabela do padrão Individual factor scores... para Retirement Data e pressione Enter.

    Captura de tela do Editor do Power Query mostrando como editar um nome de tabela em Configurações de Consulta.

  6. Os dados existentes são ordenados por uma pontuação ponderada, como descrito na página da web de origem em Metodologia. Em seguida, classificaremos a tabela nesta coluna para comparar a classificação da pontuação personalizada com a pontuação existente.

  7. Na faixa de opções Adicionar Coluna, selecione Coluna Personalizada.

    Captura de tela da faixa de opções Adicionar Coluna do Editor do Power Query com o botão Coluna Personalizada realçado.

  8. Na caixa de diálogo Coluna Personalizada, no campo Nome da nova coluna, insira Nova pontuação. Na Fórmula de coluna personalizada, insira os seguintes dados:

    ( [Quality of life] + [Cost of housing] + [Public health] + [Crime] + [Taxes] + [Weather] + [#"Non-housing cost of living"] ) / 7

  9. Verifique se a mensagem de status indica Nenhum erro de sintaxe foi detectado e, em seguida, selecione OK.

    Captura de tela da caixa de diálogo Coluna Personalizada do Editor do Power Query mostrando o novo nome da coluna, a fórmula de coluna personalizada e nenhum erro de sintaxe.

  10. Em Configurações de Consulta, a lista ETAPAS APLICADAS mostra a nova etapa Personalizada Adicionada que acabamos de definir.

    Captura de tela do painel Configurações de Consulta do Editor do Power Query mostrando a lista Etapas Aplicadas com as ações até o momento.

Ajustar os dados

Antes de trabalharmos com essa consulta, vamos fazer algumas alterações para ajustar os dados:

  • Ajustar as classificações removendo uma coluna.

    Por exemplo, suponha que Clima não seja um fator em nossos resultados. Remover essa coluna da consulta não afeta os outros dados.

  • Corrija os erros.

    Como removemos uma coluna, precisamos ajustar nossos cálculos na coluna Nova pontuação alterando sua fórmula.

  • Classificar os dados.

    Classifique os dados com base na coluna Nova pontuação e compare com a coluna Classificação existente.

  • Substituir os dados.

    Destacaremos como substituir um valor específico e como inserir uma etapa aplicada.

Essas alterações são descritas nas etapas a seguir.

  1. Para remover a coluna Clima, selecione a coluna, escolha a guia Página Inicial na faixa de opções e, em seguida, escolha Remover Colunas.

    Captura de tela do menu Página Inicial do Editor do Power Query com o botão Remover Colunas realçado.

    Observação

    Os valores de Nova pontuação não foram alterados devido à ordenação das etapas. O Editor do Power Query registra as etapas sequencialmente, mas independentemente, umas das outras. Para aplicar ações em uma sequência diferente, você pode mover cada etapa aplicada para cima ou para baixo.

  2. Clique com o botão direito do mouse em uma etapa para ver o menu de contexto.

    Captura de tela do menu de contexto Etapas Aplicadas do Editor do Power Query.

  3. Selecione Mover antes no menu de contexto para mover a última etapa, Colunas Removidas para logo cima da etapa Personalização Adicionada. Você também pode usar o mouse para mover um passo para a posição desejada.

    Captura de tela da lista Etapas Aplicadas do Editor do Power Query com a etapa Colunas Removidas movida acima da etapa Coluna Personalizada.

  4. Selecione a etapa Personalização Adicionada.

    Observe que a coluna Nova pontuação agora mostra Erro em vez do valor calculado.

    Captura de tela do Editor do Power Query e da coluna Nova pontuação que contém valores de Erro.

    Existem várias maneiras de obter mais informações sobre cada erro. Se você selecionar a célula sem clicar na palavra Erro, o Editor do Power Query exibirá as informações de erro.

    Captura de tela de Editor do Power Query mostrando a coluna Nova pontuação com detalhes do Erro.

    Se você selecionar a palavra Erro diretamente, o Editor do Power Query criará uma Etapa Aplicada no painel Configurações de Consulta e exibirá as informações sobre o erro. Como não precisamos exibir informações do erro em nenhum lugar, selecione Cancelar.

  5. Para corrigir os erros, há duas alterações necessárias, remover o nome da coluna Clima e alterar o divisor de 7 para 6. Você pode fazer essas alterações de duas maneiras:

    1. Clique com o botão direito do mouse na etapa Personalizada Adicionada e selecione Editar Configurações ou clique no ícone de engrenagem ao lado do nome da etapa para abrir a caixa de diálogo Coluna Personalizada usada para criar a coluna Nova pontuação. Edite a fórmula, como descrito anteriormente, até que se pareça com esta:

      Captura de tela da caixa de diálogo Coluna Personalizada do Editor do Power Query com erros de fórmula corrigidos.

    2. Selecione a coluna Nova pontuação e, em seguida, exiba a fórmula de dados da coluna habilitando a caixa de seleção Barra de Fórmulas na guia Exibir.

      Captura de tela de Editor do Power Query mostrando a coluna Nova pontuação e sua fórmula de dados com erros corrigidos.

      Edite a fórmula, como descrito anteriormente, até que se pareça com esta, e pressione Enter.

      = Table.AddColumn(#"Removed Columns", "New score", each ( [Quality of life] + [Cost of housing] + [Public health] + [Crime] + [Taxes] + [#"Non-housing cost of living"] ) / 6)

    O Editor do Power Query substitui os dados pelos valores revisados e a etapa Personalização Adicionada é concluída sem erros.

    Observação

    Selecione também Remover Erros usando a faixa de opções ou o menu de atalho, que remove as linhas com erros. No entanto, neste tutorial, queremos preservar todos os dados na tabela.

  6. Classifique os dados com base na coluna Nova pontuação. Primeiro, selecione a última etapa aplicada, Personalização Adicionada, para exibir os dados mais recentes. Em seguida, selecione a lista suspensa localizada ao lado do cabeçalho de coluna Nova pontuação e escolha Classificar em Ordem Decrescente.

    Captura de tela de Editor do Power Query mostrando a coluna Nova pontuação com Classificar em Ordem Decrescente.

    Agora os dados estão classificados de acordo com a Nova pontuação. Você pode selecionar uma etapa aplicada em qualquer lugar na lista e continuar formatando os dados nesse ponto na sequência. O Editor do Power Query insere de maneira automática uma nova etapa diretamente após a etapa aplicada selecionada no momento.

  7. Em ETAPAS APLICADAS, selecione a etapa que precede a coluna personalizada, que é a etapa Colunas Removidas. Aqui, substituiremos o valor da classificação Custo de habitação no Oregon. Clique com o botão direito na célula apropriada que contém o valor Custo de habitação do Oregon e, em seguida, selecione Substituir Valores. Observe qual Etapa Aplicada está selecionada no momento.

    Captura de tela da janela Editor do Power Query mostrando a coluna Custo de habitação com o item de menu Substituir Valores com o botão direito do mouse realçado.

  8. Selecione Inserir.

    Como estamos inserindo uma etapa, o Editor do Power Query nos alerta que as etapas seguintes podem causar uma interrupção na consulta.

    Captura de tela da caixa de diálogo Inserir verificação da Etapa do Editor do Power Query.

  9. Altere o valor dos dados para 100,0.

    O Editor do Power Query substitui os dados do Oregon. Quando você cria uma etapa aplicada, o Editor do Power Query a nomeia com base na ação; nesse caso, Valor Substituído. Se você tiver mais de uma etapa com o mesmo nome na consulta, o Editor do Power Query acrescenta um número crescente ao nome de cada etapa aplicada subsequente.

  10. Selecione a última Etapa Aplicada, Linhas Classificadas.

    Observe que os dados foram alterados em relação à nova classificação do Oregon. Essa alteração ocorre porque inserimos a etapa Valor Substituído na localização correta, antes da etapa Personalização Adicionada.

    Agora formatamos nossos dados na medida necessária. Em seguida, vamos nos conectar a outra fonte de dados e combinar dados.

Combinar dados

Os dados sobre vários estados são interessantes e serão úteis para a criação de mais consultas e esforços de análise. Entretanto, a maioria dos dados sobre os estados usam uma abreviação de duas letras para códigos de estado, em vez de utilizar o nome completo do estado. Precisamos de uma maneira de associar os nomes de estados às respectivas abreviações.

Há outra fonte de dados pública que fornece essa associação, mas ela precisa de uma quantidade razoável de formatação antes que possamos conectá-la à nossa tabela de aposentadoria. Para formatar os dados, siga estas etapas:

  1. Na faixa de opções Página Inicial no Editor do Power Query, selecione Nova Fonte > Web.

  2. Insira o endereço do site de abreviações de estados, https://en.wikipedia.org/wiki/List_of_U.S._state_and_territory_abbreviations , e, em seguida, selecione OK.

    O Navegador exibirá o conteúdo do site.

    Captura de tela da página Navegador do Editor do Power Query mostrando a tabela Códigos e abreviações selecionada.

  3. Selecione Códigos e abreviações para estados dos EUA, distrito federal, territórios e outras regiões.

    Dica

    Será necessário uma formatação para reduzir os dados dessa tabela até o que desejamos. Há uma maneira mais rápida ou mais fácil de realizar as seguintes etapas? Sim, podemos criar uma relação entre as duas tabelas e formatar os dados com base nessa relação. As etapas de exemplo a seguir são úteis para aprender a trabalhar com tabelas. No entanto, as relações podem ajudá-lo a usar rapidamente os dados de várias tabelas.

Para colocar os dados em forma, siga estas etapas:

  1. Remova a linha superior. Como ela é o resultado do modo como a tabela da página web foi criada, não precisamos dela. Na faixa de opções Página Inicial, selecione Remover Linhas > Remover Primeiras Linhas.

    Captura de tela de Editor do Power Query realçando a lista suspensa Remover Linhas e o item Remover Linhas Superiores.

    A caixa de diálogo Remover Linhas Superiores é exibida. Especifique 1 linha para ser removida.

  2. Como a tabela Dados de Aposentadoria não contém informações de Washington D.C. ou dos territórios, precisamos filtrá-los de nossa lista. Selecione na lista suspensa da coluna Status da região e desmarque todas as caixas de seleção, exceto Estado e Estado (oficialmente Commonwealth).

    Captura de tela de Editor do Power Query mostrando um filtro de coluna com apenas o valor Estado selecionado.

  3. Remova todas as colunas desnecessárias. Como precisamos apenas do mapeamento de cada estado para sua abreviação oficial de duas letras (Nome e ANSI), podemos remover as outras colunas. Primeiro, selecione a coluna Nome e mantenha pressionada a tecla CTRL e selecione a coluna ANSI. Na guia Página Inicial da faixa de opções, selecione Remover Colunas > Remover Outras Colunas.

    Captura de tela de Editor do Power Query realçando a lista suspensa Remover Colunas e o item Remover Outras Colunas.

    Observação

    A sequência de etapas aplicadas no Editor do Power Query é importante e afeta a maneira como os dados são moldados. Também é importante considerar como uma etapa pode impactar outra etapa subsequente. Por exemplo, se você remover uma etapa das etapas aplicadas, as etapas subsequentes poderão não se comportar como pretendido originalmente.

    Observação

    Ao redimensionar a janela do Editor do Power Query para diminuir a largura, alguns itens de faixa de opções são condensados para fazer o melhor uso do espaço visível. Ao aumentar a largura da janela do Editor do Power Query, os itens da faixa de opções são expandidos para fazer o melhor uso da área aumentada da faixa de opções.

  4. Renomeie as colunas e a tabela. Há algumas maneiras de renomear uma coluna: primeiro selecione a coluna e depois selecione Renomear na guia Transformar na faixa de opções ou clique com o botão direito do mouse e selecione Renomear. A imagem a seguir mostra ambas as opções, mas você precisa escolher apenas uma.

    Captura de tela de Editor do Power Query realçando o botão Renomear e também o item Renomear com o botão direito do mouse.

  5. Renomeie as colunas para State Name e State Code. Para renomear a tabela, insira o Códigos de Estado no campo Nome no painel Configurações de Consulta.

    Captura de tela da janela Editor do Power Query mostrando os resultados da formatação de dados de origem de códigos de estado em uma tabela.

Combinar consultas

Agora que formatamos a tabela Códigos de Estado da maneira que desejamos, vamos combinar essas duas tabelas, ou consultas, em uma só. Como as tabelas que temos agora são o resultado das consultas que aplicamos aos dados, elas são geralmente chamadas de consultas.

Há duas maneiras principais de combinar consultas: mesclando e acrescentando.

  • Para uma ou mais colunas que você deseja adicionar a outra consulta, você mescla as consultas.
  • Para uma ou mais linhas de dados que você deseja adicionar a uma consulta existente, você acrescenta a consulta.

Nesse caso, desejamos mesclar as consultas:

  1. No painel esquerdo do Editor do Power Query, selecione a consulta na qual deseja mesclar a outra consulta. Nesse caso, são Dados de Aposentadoria.

  2. Selecione Mesclar Consultas > Mesclar Consultas na guia Página Inicial na faixa de opções.

    Captura de tela da lista suspensa Consultas de Mesclagem do Editor do Power Query com o item Mesclar Consultas realçado.

    Talvez você precise definir os níveis de privacidade, a fim de garantir que os dados sejam combinados sem incluir nem transferir dados que você não deseja transferir.

    A janela Mesclar será exibida. Ela solicita a seleção da tabela que você deseja mesclar com a tabela selecionada e as colunas correspondentes a serem usadas para a mesclagem.

  3. Selecione State na tabela Dados de Aposentadoria e, em seguida, a consulta Códigos de Estado.

    Quando você seleciona uma coluna correspondente, o botão OK é habilitado.

    Captura de tela da caixa de diálogo Mesclagem do Editor do Power Query.

  4. Selecione OK.

    O Editor do Power Query cria uma coluna ao final da consulta, que consiste no conteúdo da tabela (consulta) que foi mesclada com a consulta existente. Todas as colunas da consulta mesclada são condensadas na coluna, mas você pode Expandir a tabela e incluir todas as colunas que quiser.

  5. Para expandir a tabela mesclada e selecionar as colunas a serem incluídas, selecione o ícone de expansão ( ).

    A janela Expandir é exibida.

    Captura de tela da caixa de diálogo Expandir coluna do Editor do Power Query mostrando a coluna Código de Estado realçada.

  6. Nesse caso, queremos apenas a coluna State Code. Selecione essa coluna, desmarque Usar nome da coluna original como prefixo e, em seguida, selecione OK.

    Se tivéssemos deixado a caixa de seleção marcada para Usar o nome da coluna original como prefixo, a coluna mesclada seria nomeada Códigos de Estado.Códigos de Estado.

    Observação

    Se você quiser explorar como trazer a tabela Códigos de Estado, poderá experimentar. Se não gostar dos resultados, basta excluir essa etapa da lista ETAPAS APLICADAS no painel Configurações de Consulta; e sua consulta retornará ao estado anterior à aplicação dessa etapa Expandir. Você pode fazer isso quantas vezes quiser, até que o processo de expansão tenha a aparência desejada.

    Agora temos uma única consulta (tabela) que combina duas fontes de dados, cada uma delas formatada de acordo com nossas necessidades. Essa consulta pode ser uma base para conexões de dados interessantes, como estatísticas de custo de habitação, qualidade de vida ou taxa de criminalidade em qualquer estado.

  7. Para aplicar as alterações e fechar o Editor do Power Query, selecione Fechar e Aplicar na guia de faixa de opções Página Inicial.

    O modelo semântico transformado aparece no Power BI Desktop, pronto para ser usado para criar relatórios.

Conteúdo relacionado

Para obter mais informações sobre o Power BI Desktop e as funcionalidades dele, confira os seguintes recursos: