Transformar colunas em linhas

No Power Query, você pode transformar colunas em pares atributo-valor, em que as colunas se tornam linhas.

Diagrama mostrando uma tabela esquerda com uma coluna em branco e linhas; 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 transformar as colunas em linhas, uma 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 seguir, em que linhas de país e colunas de data criam uma matriz de valores, é difícil analisar os dados de maneira escalonável.

Captura de tela de uma tabela que contém uma coluna Country definida no tipo de dados Texto e colunas com as datas 1º de junho de 2023, 1º de julho de 2023 e 1º de agosto de 2023 definidas com o tipo de dados Número inteiro. A coluna País contém EUA na linha 1, Canadá na linha 2 e Panamá na linha 3.

No entanto, você pode transformar a tabela 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 País contém EUA nas três primeiras linhas, Canadá nas três linhas seguintes e Panamá nas três últimas linhas. A coluna Atributo contém a data 1º de junho de 2023 na primeira, quarta e sétima linhas, a data 1º de julho de 2023 na segunda, quinta e oitava linhas e a data 1º de agosto de 2023 na terceira, sexta e nona linhas.

O importante nessa transformação é que você tem um conjunto de datas na tabela, que deve fazer parte de uma única coluna. O respectivo valor de 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 de coluna que transformamos em linhas.
• Valor: os valores que estavam abaixo de cada um dos títulos de coluna transformados em linhas.

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 na 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 transformar em linhas. Você pode selecionar Ctrl para escolher quantas colunas precisar. Para esse cenário, você deseja selecionar todas as colunas, exceto aquela chamada País. 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 agosto de 2023 selecionadas e o comando Transformar colunas em linhas selecionado no menu de atalho.

O resultado dessa operação produz o resultado mostrado na imagem a seguir.

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 País contém EUA nas três primeiras linhas, Canadá nas três linhas seguintes e Panamá nas três últimas linhas. A coluna Atributo contém a data 1º de junho de 2023 na primeira, quarta e sétima linhas, a data 1º de julho de 2023 na segunda, quinta e oitava linhas e a data 1º de agosto de 2023 na terceira, sexta e nona linhas. Além disso, a entrada Transformar colunas em linhas é enfatizada no painel Configurações de consulta e o código da linguagem M é mostrado na barra de fórmulas.

Considerações especiais

Depois de criar sua consulta com base nas etapas anteriores, imagine que sua tabela inicial seja atualizada para ficar semelhante à captura de tela a seguir.

Captura de tela da tabela com as mesmas colunas de data Country, 1º de junho de 2023, 1º de julho de 2023 e agosto de 2023, com a adição de uma coluna de data de 1º de setembro de 2023. A coluna País ainda contém os valores EUA, Canadá e Panamá, mas também tem Reino Unido adicionado à quarta linha e México adicionado à quinta linha.

Observe que você está adicionando uma nova coluna para a data de 1º de setembro de 2023 (1/9/2023) e duas novas linhas para os países/regiões do 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 coluna nova adicionada à tabela de origem também é transformada em linha.

A imagem a seguir mostra como fica sua 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 País contêm EUA, o segundo grupo de quatro linhas contém Canadá, o terceiro grupo de quatro linhas contém Panamá, o quarto grupo de quatro linhas contêm Reino Unido, e o quinto grupo de quatro linhas contém México. A coluna Atributo contém 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. Essa operação é aquela em que Transformar outras colunas em linhas entra em jogo.

O resultado dessa operação produzir 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 País contém EUA nas três primeiras linhas, Canadá nas três linhas seguintes e Panamá nas três últimas linhas. A coluna Atributo contém a data 1º de junho de 2023 na primeira, quarta e sétima linhas, a data 1º de julho de 2023 na segunda, quinta e oitava linhas e a data 1º de agosto de 2023 na terceira, sexta e nona linhas.

Observação

Essa transformação é crucial para consultas que têm um número desconhecido de colunas. A operação transformará em linhas todas as colunas da tabela, exceto as que você selecionou. Essa é uma solução ideal caso a fonte de dados do seu cenário obtenha novas colunas de data em uma atualização, pois elas serão escolhidas 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 são transformadas em linhas, exceto as que foram selecionadas anteriormente.

Para ilustrar esse processo, digamos que você tenha 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 para 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, que produzir 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 com o tipo de dados Texto. A coluna Valor é definida com o tipo de dados Valor inteiro. As quatro primeiras linhas da coluna País contêm EUA, o segundo grupo de quatro linhas contém Canadá, o terceiro grupo de quatro linhas contém Panamá, o quarto grupo de quatro linhas contêm Reino Unido, e o quinto grupo de quatro linhas contém México. A coluna Atributo contém 1º de junho de 2023, 1º de julho de 2023 e agosto de 2023 e 1º de setembro de 2023 nas quatro primeiras linhas, que são repetidas 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 transformadas em linhas, 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 produzir a mesma saída que os 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 País contém EUA nas três primeiras linhas, Canadá nas três linhas seguintes e Panamá nas três últimas linhas. A coluna Atributo contém a data 1º de junho de 2023 na primeira, quarta e sétima linhas, a data 1º de julho de 2023 na segunda, quinta e oitava linhas e a data 1º de agosto de 2023 na terceira, sexta e nona linhas.

Considerações especiais

Depois de fazer uma atualização, se nossa tabela de origem mudar para ter uma nova coluna 1/9/2020 e novas linhas para Reino Unido e México, a saída da consulta é diferente dos exemplos anteriores. Digamos que, após uma atualização, nossa tabela original mude para a tabela na imagem a seguir.

A saída da nossa consulta é semelhante à 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.