Como transformar campos de uma coluna em colunas independentes

[Jorge Borges]

Aoba, pessoal!

Então, estou trabalhando num modesto projeto no meu trabalho e me deparei com a seguinte questão: todos os dados que deveriam ser uma coluna estão dispostos repetidamente nos campos da Coluna 1 da query. E na Coluna 2 estão os respectivos valores de cada campo da Coluna 1. Eu preciso que cada campo da Coluna 1 seja uma coluna independente.

Como os dados originais são sensíveis, eu criei um pequeno dataset de exemplo (ver anexo) para demonstrar como os dados estão (INPUT) e como deveriam ficar (OUTPUT).

Na imagem abaixo é possível visualizar melhor a questão: os campos que deveriam ser colunas se repetem em blocos de 4 ao longo da coluna 1.

 

Eu na verdade já encontrei uma solução (vou postá-la nas respostas deste post), porém ela funciona bem quando se tem poucos campos para transformar em colunas, mas se houver muitos campos acaba ficando muito trabalhoso e deselegante. Então gostaria da ajuda de vocês para verificar se há algum modo mais sofisticado e prático de fazer.

Agradeço desde já!

dataset exemplo.xlsx

[Thassio P D Nascimento]

Olá Jorge! Veja como ficou o meu...

 

Utilizando essas Etapas:

 

- Você pode depois otimizar alguns passos como remoção de colunas...

Vou anexar o pbix

pbixJorge.pbix

[Jorge Borges]

Aoba!

Então, conforme comentei no post acima, eu encontrei mais ou menos uma solução para a questão, porém ela não ficou muito boa caso haja muitos campos para transformar em colunas, que é exatamente o caso do meu dataset original.

obs.: eu uso o PBI em inglês, então não sei o nome em PT-BR da maioria dos recursos da ferramenta, então vou utilizar os termos de acordo com versão EN mesmo.

O que eu fiz foi criar uma coluna condicional para cada campo que precisa virar uma coluna. Por exemplo, na primeira coluna condicional é feito o teste: if Column1 = "Projeto" then Column2. Na segunda coluna condicional:  if Column1 = "Cliente" then Column2, e assim por diante.

Depois utilizei o recurso Fill Up para completar os campos nulos acima

Em seguida criei uma index column e depois criei uma última coluna condicional fazendo o seguinte teste: if [Column1] = "Projeto" then [Index] else null

E por fim, usei desta vez usei o recurso Fill Down para completar os campos nulos desta última coluna condicional e em seguida removi os valores duplicados dela. E após excluir as demais colunas, cheguei ao resultado abaixo:

 Porém aqui temos dois problemas:

1. Nesse exemplo, ter que criar uma coluna condicional para os 4 campos foi de boa, porém fazer isso para em um cenário com muitos campos para virar colunas será muito dispendioso; 
2. Ter que renomear todas as colunas com o nome correto, o ideal seria já conseguir puxar essa informação diretamente do respectivo campo que originou a coluna

Acredito que tenha algum processo mais refinado para aplicar, só não sei qual rs. Conto com a ajuda de vocês.

Editado Março 17, 2021 por Jorge Borges

[Thassio P D Nascimento]

Para o Pivot Columns dar certo aqui precisamos adicionar uma coluna índice para termos valores únicos e não repetidos como na Columns1  ..

 

-- Selecione a Column1 e vai Coluna Dinâmica, a coluna de valores será a Column2 e selecione Não agregar em Opções Avançadas

 

O resultado será esse abaixo, aqui você irá selecionar as colunas Projeto, Cliente e Resp. Técnico e aplique a transformação, Preencher para Baixo e depois para Cima (Siga essa ordem)

 

-- Após essa transformação e com esse resultado abaixo, filtre os vazios da Coluna Segmento, e exclua a coluna índice.

Caso a resposta tenha te ajudado, resolvido, favor marcar como melhor resposta

 

E também pode contribuir com a reação.

[Thassio P D Nascimento]

5 minutos atrás, Thassio P D Nascimento disse:

Para o Pivot Columns dar certo aqui precisamos adicionar uma coluna índice para termos valores únicos e não repetidos como na Columns1  ..

 

-- Selecione a Column1 e vai Coluna Dinâmica, a coluna de valores será a Column2 e selecione Não agregar em Opções Avançadas

 

O resultado será esse abaixo, aqui você irá selecionar as colunas Projeto, Cliente e Resp. Técnico e aplique a transformação, Preencher para Baixo e depois para Cima (Siga essa ordem)

 

-- Após essa transformação e com esse resultado abaixo, filtre os vazios da Coluna Segmento, e exclua a coluna índice.

Caso a resposta tenha te ajudado, resolvido, favor marcar como melhor resposta

 

E também pode contribuir com a reação.

Dê uma olhada nessa documentação da Microsoft: https://docs.microsoft.com/pt-br/power-query/pivot-columns#:~:text=Error%3A havia muitos elementos na,combinação de país e data.

E no curso, nos exercícios de PQ o Léo tem uns bons exemplos desse.

[Jorge Borges]

11 horas atrás, Thassio P D Nascimento disse:

Olá Jorge! Veja como ficou o meu...

 

Utilizando essas Etapas:

 

- Você pode depois otimizar alguns passos como remoção de colunas...

Vou anexar o pbix

pbixJorge.pbix 20 kB · 1 download

Boa noite.

Obrigado, funcionou muito bem!

Ainda não devo ter chegado nessa parte do curso, to fazendo bem aos poucos!

Uma coisa que reparei no dataset original é que as colunas que deveriam ser dinamizadas não tinham um padrão de repetição igual o dataset de exemplo e conforme os cases do link que você mandou, e isso complicou na hora de fazer o Fill Down/Up. Então antes de aplicar a feature Coluna Dinâmica eu tive que classificar a coluna a ser dinamizada em ordem crescente, isso criou o padrão ideal para prosseguir com os passos seguintes.

Valeu!