Anular dinamização das colunas
No Power Query, pode transformar colunas em pares 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. Com as colunas não dinâmicas, a tabela direita do diagrama contém uma coluna e linhas em branco, uma coluna Atributos com nove linhas com A1, A2 e A3 repetidas três vezes e uma coluna Valores com valores V1 a V9.
Por exemplo, dada uma tabela como a seguinte, onde linhas de país e colunas de data criam uma matriz de valores, é difícil analisar os dados de forma escalável.
Captura de ecrã 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 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.
Em vez disso, você pode transformar a tabela em uma tabela com colunas não dinâmicas, conforme mostrado na imagem a seguir. Na tabela transformada, é mais fácil usar a data como um atributo para filtrar.
Captura de ecrã da tabela que contém uma coluna País definida como o tipo de dados Texto, uma coluna Atributo definida como 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 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 respetivo valor para cada data e país deve estar em uma coluna diferente, criando efetivamente um par atributo-valor.
O Power Query cria sempre o par atributo-valor utilizando duas colunas:
- Atributo: O nome dos cabeçalhos de coluna que não foram pivotados.
- Valor: Os valores que estavam abaixo de cada um dos cabeçalhos de coluna não pivotados.
Há vários lugares na interface do usuário onde você pode encontrar colunas Unpivot. Pode clicar com o botão direito do rato nas colunas que pretende desdinamizar ou pode selecionar o comando no separador Transformar no friso.
Há três maneiras de despivotar colunas de uma tabela:
- Despivotar colunas
- Despivotar outras colunas
- Despivotar apenas colunas selecionadas
Para o cenário descrito anteriormente, primeiro você precisa selecionar as colunas que deseja despivotar. Você pode selecionar Ctrl ao selecionar quantas colunas precisar. Para esse cenário, você deseja selecionar todas as colunas, exceto a chamada País. Depois de selecionar as colunas, clique com o botão direito do rato em qualquer uma das colunas selecionadas e, em seguida, selecione Desdinamizar colunas.
Captura de tela da tabela com as colunas de 1º de junho de 2023, 1º de julho de 2023 e 1º de agosto de 2023 selecionadas e o comando Despivotar colunas selecionado no menu de atalho."
O resultado dessa operação produz o resultado mostrado na imagem a seguir.
Depois de criar sua consulta a partir 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 de 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 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 sua consulta, observe que a operação é feita na coluna atualizada, mas não afeta a coluna que não foi selecionada originalmente (País, neste exemplo). Isso significa que qualquer nova coluna adicionada à tabela de origem também não será pivotada.
A imagem a seguir mostra a aparência da sua consulta após a atualização com a nova tabela de origem atualizada.
Captura de ecrã da tabela com as colunas País, Atributo e Valor. As quatro primeiras linhas da coluna País 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 quartas 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.
Você também pode selecionar as colunas que não deseja despivotar e despivotar o restante das colunas na tabela. Esta operação é onde Unpivot outras colunas entra em jogo.
O resultado dessa operação produz exatamente o mesmo resultado que o obtido nas colunas Unpivot.
Captura de ecrã da tabela que contém uma coluna País definida como o tipo de dados Texto, uma coluna Atributo definida como 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 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 despivotará todas as colunas da tabela, exceto as que você selecionou. Esta é uma solução ideal se a fonte de dados do seu cenário tiver novas colunas de data em uma atualização, porque elas serão captadas e não pivotadas.
Semelhante à operação Unpivot columns , se a consulta for atualizada e mais dados forem coletados da fonte de dados, todas as colunas serão despivotadas, 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 ecrã 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, em seguida, selecionar Despivotar outra coluna, que produz o seguinte resultado.
Captura de ecrã da tabela com as colunas País, Atributo e Valor. As colunas País e Atributo são definidas como o tipo de dados Texto. A coluna Valor é definida como o tipo de dados Valor inteiro. As quatro primeiras linhas da coluna País 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 quartas 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 são repetidas para cada país.
O objetivo desta última opção é apenas despivotar colunas específicas da tabela. 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 desdinamizar as colunas selecionadas.
Para executar essa operação, selecione as colunas a serem desdinâmicas, que neste exemplo são todas as colunas, exceto a coluna País . Em seguida, clique com o botão direito do rato em qualquer uma das colunas selecionadas e, em seguida, selecione Desdinamizar apenas as colunas selecionadas.
Observe como essa operação produz a mesma saída que os exemplos anteriores.
Captura de ecrã da tabela que contém uma coluna País definida como o tipo de dados Texto, uma coluna Atributo definida como 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 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.
Depois de fazer uma atualização, se nossa tabela de origem mudar para ter uma nova coluna 9/1/2020 e novas linhas para 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 da nossa consulta é semelhante à imagem a seguir.
Parece assim porque a operação unpivot foi aplicada apenas nas colunas 6/1/2020, 7/1/2020 e 8/1/2020 , portanto, a coluna com o cabeçalho 9/1/2020 permanece inalterada.