Nota
Este é um de uma série de artigos detalhando soluções enviadas para o desafio Podcast 2316.
Um dos problemas com minha solução é que a sequência final das categorias não correspondia necessariamente à sequência original das colunas. Percebi isso bem no final do meu vídeo e, como não era particularmente importante, não me preocupei com isso.
No entanto, Josh Johnson enviou uma solução que resolveu o problema. Quando Josh disse que usou uma coluna de índice, presumi que fosse como o Índice e o Módulo no Power Query: Numere grupos de registros de 1 a 5 repetidamente. Mas o uso de Josh era completamente diferente.
Nota: O MVP do Excel John MacDougall também usou esse método, mas concatenou a coluna do índice ao final da descrição da categoria. Veja o vídeo de John aqui: https://www.youtube.com/watch?v=Dqmb6SEJDXI e leia mais sobre seu código aqui: MVPs do Excel atacam o problema de limpeza de dados no Power Query.
No início do processo, quando Josh ainda tinha apenas seis registros, ele adicionou um índice começando em 1. Josh clicou na barra de fórmulas e renomeou a coluna Índice como Categoria.
A coluna Categoria era a nova última coluna. Ele usou Move, to Beginning para movê-lo para ser o primeiro:
Depois disso, muitas outras etapas acontecem. São etapas inovadoras, mas foram abordadas principalmente em outros artigos até agora. Depois de muitas dessas etapas, comecei a pensar que os números das categorias de 1 a 6 eram apenas um erro. Achei que possivelmente Josh iria excluí-los sem usá-los.
Josh não dinamiza, depois coluna condicional, preenche, gira e adiciona o total. Ele nunca parece usar essa coluna de categoria. Depois de muitas etapas, ele está aqui:
Mas então, nas etapas finais, Josh classifica os dados por Nome do funcionário e depois Categoria!
Nesse ponto, ele pode excluir a coluna Categoria. A diferença final: o PTO vem antes do Projeto A, assim como nas colunas originais. É um belo toque.
Também destacarei que Josh enviou um vídeo dele passando por essas etapas. Parabéns a Josh por usar atalhos de teclado dentro do Power Query!
Aqui está o código de Josh:
let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Promoted Headers" = Table.PromoteHeaders(Source, (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Dept. Total", type number), ("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", Int64.Type), ("Employee 1", type number), ("Q1_1", type number), ("Q2_2", type number), ("Q3_3", Int64.Type), ("Q4_4", Int64.Type), ("Employee 2", Int64.Type), ("Q1_5", Int64.Type), ("Q2_6", Int64.Type), ("Q3_7", Int64.Type), ("Q4_8", Int64.Type), ("Employee 3", Int64.Type), ("Q1_9", Int64.Type), ("Q2_10", Int64.Type), ("Q3_11", Int64.Type), ("Q4_12", Int64.Type), ("Employee 4", type number), ("Q1_13", type number), ("Q2_14", type number), ("Q3_15", type number), ("Q4_16", Int64.Type))), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Category", 1, 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",("Category", "Category Description", "Dept. Total", "Q1", "Q2", "Q3", "Q4", "Employee 1", "Q1_1", "Q2_2", "Q3_3", "Q4_4", "Employee 2", "Q1_5", "Q2_6", "Q3_7", "Q4_8", "Employee 3", "Q1_9", "Q2_10", "Q3_11", "Q4_12", "Employee 4", "Q1_13", "Q2_14", "Q3_15", "Q4_16")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category", "Category Description"), "Attribute", "Value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", (("Attribute", each Text.BeforeDelimiter(_, "_"), type text))), #"Added Conditional Column" = Table.AddColumn(#"Extracted Text Before Delimiter", "Employee Name", each if not Text.StartsWith((Attribute), "Q") then (Attribute) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee Name")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Attribute) = "Q1" or (Attribute) = "Q2" or (Attribute) = "Q3" or (Attribute) = "Q4") and ((Employee Name) "Dept. Total")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"(Attribute)), "Attribute", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Sorted Rows" = Table.Sort(#"Inserted Sum",(("Employee Name", Order.Ascending), ("Category", Order.Ascending))), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",("Category")) in #"Removed Columns"Volte para a página principal do desafio Podcast 2316.
Leia o próximo artigo desta série: MVPs do Excel atacam o problema de limpeza de dados no Power Query.
