Transformar colunas em linhas
No Power Query, você pode transformar colunas em pares de atributo-valor, em que as colunas se tornam linhas.
Diagrama mostrando a tabela esquerda com uma coluna e linhas em branco e os valores de Atributos A1, A2 e A3 como cabeçalhos de coluna. Nesta tabela, a coluna A1 contém os valores V1, V4 e V7. A coluna A2 contém os valores V2, V5 e V8. A coluna A3 contém os valores V3, V6 e V9. Após a transformação das colunas em linhas, a tabela direita do diagrama contém uma coluna em branco e linhas, uma coluna Atributos com nove linhas com A1, A2 e A3 repetidos três vezes e uma coluna Valores com valores V1 a V9.
Por exemplo, considerando uma tabela como a seguinte, em que linhas de país e colunas de data criam uma matriz de valores, é difícil analisar os dados de forma escalonável.
Captura de tela de uma tabela que contém uma coluna País definida no tipo de dados Texto e três colunas com as datas 1 de junho de 2023, 1 de julho de 2023 e 1 de agosto de 2023 definidas como tipo de dados Número Inteiro. A coluna Country contém EUA na linha 1, Canadá na linha 2 e Panamá na linha 3.
No entanto, você pode fazer a transformação em uma tabela com colunas transformadas em linhas, conforme mostrado na imagem a seguir. Na tabela transformada, é mais fácil usar a data como um atributo para filtrar.
Captura de tela da tabela que contém uma coluna de País como o tipo de dados Texto, uma coluna de atributo definida com o tipo de dados Texto e uma coluna Valor definida como o tipo de dados Número inteiro. A coluna Country contém EUA nas três primeiras linhas, Canadá nas três próximas linhas e Panamá nas últimas três linhas. A coluna Attribute contém a data de 1º de junho de 2023 na primeira, quarta e sétima linhas, a data de 1º de julho de 2023 na segunda, quinta e oitava linhas, e a data de 1º de agosto de 2023 na terceira, sexta e nona linhas.
A chave nessa transformação é que você tem um conjunto de datas na tabela que devem fazer parte de uma única coluna. O respectivo valor para cada data e país deve estar em uma coluna diferente, criando efetivamente um par de atributo-valor.
O Power Query sempre cria o par atributo-valor usando duas colunas:
- Atributo: o nome dos títulos das colunas que foram transformadas em linhas.
- Valor: os valores que estavam abaixo dos títulos de cada coluna transformada em linha.
Há vários locais na interface do usuário em que você pode encontrar Transformar colunas em linhas. Você pode clicar com o botão direito do mouse nas colunas que deseja transformar em linhas ou selecionar o comando na guia Transformar da faixa de opções.
Há três maneiras de transformar colunas em linhas em uma tabela:
- Transformar colunas em linhas
- Transformar outras colunas em linhas
- Transformar somente as colunas selecionadas em linhas
Transformar colunas em linhas
Para o cenário descrito anteriormente, primeiro você precisa selecionar as colunas que deseja despivotar. Você pode pressionar Ctrl e selecionar quantas colunas forem necessárias. Para esse cenário, você deseja selecionar todas as colunas, exceto aquela chamada Country. Depois de selecionar as colunas, clique com o botão direito do mouse em qualquer uma das colunas selecionadas e selecione Transformar colunas em linhas.
Captura de tela da tabela com as colunas 1º de junho de 2023, 1º de julho de 2023 e 1º de agosto de 2023 selecionadas, e o comando Transformar colunas em linhas selecionado no menu de atalho."
O resultado dessa operação gera o resultado mostrado na imagem a seguir.
Considerações especiais
Depois de criar sua consulta das etapas anteriores, imagine que sua tabela inicial seja atualizada para se parecer com a captura de tela a seguir.
Captura de tela da tabela com as mesmas colunas de data do país originais: 1º de junho de 2023, 1º de julho de 2023, 1º de agosto de 2023, com a adição de uma coluna de data de 1º de setembro de 2023. A coluna Country ainda contém os valores dos EUA, Canadá e Panamá, mas também tem o Reino Unido adicionado à quarta linha e o México adicionado à quinta linha.
Observe que você está adicionando uma nova coluna para a data de 1º de setembro de 2023 (01/09/2023) e duas novas linhas para os países/regiões Reino Unido e México.
Se você atualizar a consulta, observe que a operação é feita na coluna atualizada, mas não afeta a coluna que não foi selecionada originalmente (Country, neste exemplo). Isso significa que qualquer nova coluna adicionada à tabela de origem também será transformada em linha.
A imagem a seguir mostra a aparência da consulta após a atualização com a nova tabela de origem atualizada.
Captura de tela da tabela com colunas País, Atributo e Valor. As quatro primeiras linhas da coluna Country contêm EUA, as segundas quatro linhas contêm o Canadá, as terceiras quatro linhas contêm o Panamá, as quartas quatro linhas contêm o Reino Unido e as quintas quatro linhas contêm o México. A coluna Atributo contém as datas de 1º de junho de 2023, 1º de julho de 2023 e agosto de 2023 nas quatro primeiras linhas, que são repetidas para cada país.
Transformar outras colunas em linhas
Você também pode selecionar as colunas que não deseja transformar em linhas e transformar em linhas o restante das colunas da tabela. É nessa operação que Transformar outras colunas em linhas entra em jogo.
O resultado dessa operação produz exatamente o mesmo resultado que você obteve em Transformar colunas em linhas.
Captura de tela da tabela que contém uma coluna de País como o tipo de dados Texto, uma coluna de atributo definida com o tipo de dados Texto e uma coluna Valor definida como o tipo de dados Número inteiro. A coluna Country contém EUA nas três primeiras linhas, Canadá nas três próximas linhas e Panamá nas últimas três linhas. A coluna Attribute contém a data de 1º de junho de 2023 na primeira, quarta e sétima linhas, a data de 1º de julho de 2023 na segunda, quinta e oitava linhas, e a data de 1º de agosto de 2023 na terceira, sexta e nona linhas.
Nota
Essa transformação é crucial para consultas que têm um número desconhecido de colunas. A operação removerá todas as colunas da tabela, exceto as que você selecionou. Essa é uma solução ideal caso a fonte de dados do seu cenário tenha recebido novas colunas de data em uma atualização, pois elas serão selecionadas e transformadas em linhas.
Considerações especiais
Semelhante à operação Transformar colunas em linhas, se a consulta for atualizada e mais dados forem coletados na fonte de dados, todas as colunas serão transformadas em linhas, exceto as que foram selecionadas anteriormente.
Para ilustrar esse processo, diga que você tem uma nova tabela como a da imagem a seguir.
Captura de tela da tabela com as colunas País, 1º de junho de 2023, 1º de julho de 2023, 1º de agosto de 2023 e 1º de setembro de 2023, com todas as colunas definidas como o tipo de dados Texto. A coluna País contém, de cima para baixo, EUA, Canadá, Panamá, Reino Unido e México.
Você pode selecionar a coluna País e selecionar Transformar outras colunas em linhas, o que produz o resultado a seguir.
Captura de tela da tabela com colunas País, Atributo e Valor. As colunas País e Atributo são definidas como o tipo de dados Texto. A coluna Valor está definida com o tipo de dados Valor inteiro. As quatro primeiras linhas da coluna Country contêm EUA, as segundas quatro linhas contêm o Canadá, as terceiras quatro linhas contêm o Panamá, as quartas quatro linhas contêm o Reino Unido, e as quintas quatro linhas contêm o México. A coluna Atributo contém 1º de junho de 2023, 1º de julho de 2023, 1º de agosto de 2023 e 1º de setembro de 2023 nas quatro primeiras linhas, que se repetem para cada país.
Transformar somente as colunas selecionadas em linhas
A finalidade dessa última opção é transformar somente colunas específicas da sua tabela em linhas. Essa opção é importante para cenários em que você está lidando com um número desconhecido de colunas da fonte de dados e deseja apenas transformar as colunas selecionadas em linhas.
Para executar essa operação, selecione as colunas a serem despivotas, que neste exemplo são todas as colunas, exceto a coluna País. Em seguida, clique com o botão direito do mouse em qualquer uma das colunas selecionadas e escolha Transformar somente as colunas selecionadas em linhas.
Observe como essa operação produz a mesma saída dos exemplos anteriores.
Captura de tela da tabela que contém uma coluna de País como o tipo de dados Texto, uma coluna de atributo definida com o tipo de dados Texto e uma coluna Valor definida como o tipo de dados Número inteiro. A coluna Country contém EUA nas três primeiras linhas, Canadá nas três próximas linhas e Panamá nas últimas três linhas. A coluna Attribute contém a data de 1º de junho de 2023 na primeira, quarta e sétima linhas, a data de 1º de julho de 2023 na segunda, quinta e oitava linhas, e 1º de agosto de 2023 na terceira, sexta e nona linhas.
Considerações especiais
Depois de atualizar, se nossa tabela de origem mudar para incluir uma nova coluna 9/1/2020 e novas linhas para o Reino Unido e México, a saída da consulta será diferente dos exemplos anteriores. Digamos que nossa tabela de origem, após uma atualização, mude para a tabela na imagem a seguir.
A saída de nossa consulta se parece com a imagem a seguir.
Isso ocorre porque a operação transformar colunas em linhas foi aplicada somente nas colunas 1/6/2020, 1/7/2020 e 8/1/2020, portanto, a coluna com o cabeçalho 1/9/2020 permanece inalterada.