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. A coluna A1 contém os valores V1, V4 e V7, a coluna A2 contém os valores V2, V5 e V8, e 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.

Tabela que contém uma coluna País definida no tipo de dados Texto e colunas 1/6/2020, 1/7/2020 e 1/8/2020 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.

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 1/6/2020 na primeira, quarta e sétima linhas, 1/7/2020 na segunda, quinta e oitava linhas e 1/8/2020 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 criará 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 acima, 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.

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

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 1/6/2020 na primeira, quarta e sétima linhas, 1/7/2020 na segunda, quinta e oitava linhas e 1/8/2020 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 acima, imagine que sua tabela inicial seja atualizada para ficar semelhante à captura de tela a seguir.

Tabela com as mesmas colunas País, 1/6/2020, 1/7/2020 e 1/8/2020, com a adição de uma coluna de 1/9/2020. 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ê adicionou uma nova coluna para a data de 1/9/2020 (1º de setembro de 2020) e duas novas linhas para os países/regiões do Reino Unido e México.

Se você atualizar a consulta, observará que a operação será feita na coluna atualizada, mas não afetará a coluna que não foi selecionada originalmente (País, neste exemplo). Isso significa que qualquer coluna nova adicionada à tabela de origem também será transformada em linha.

A imagem a seguir mostra como ficará sua consulta após a atualização com a nova tabela de origem atualizada.

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 01/06/2020, 01/07/2020, 01/08/2020 e 01/09/2020 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.

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 1/6/2020 na primeira, quarta e sétima linhas, 1/7/2020 na segunda, quinta e oitava linhas e 1/8/2020 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 serão transformadas em linhas, exceto as que foram selecionadas anteriormente.

Para ilustrar isso, digamos que você tenha uma nova tabela como a da imagem a seguir.

Tabela com as colunas País, 1/6/2020, 1/7/2020, 1/8/2020 e 1/9/2020, com todas as colunas definidas com 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.

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 01/06/2020, 01/07/2020, 01/08/2020 e 01/09/2020 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. Isso é 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.

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 1/6/2020 na primeira, quarta e sétima linhas, 1/7/2020 na segunda, quinta e oitava linhas e 1/8/2020 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 será 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 será 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.