Desafio "Como você limparia estes dados" de Bill - Dicas do Excel

Quando faço um seminário ao vivo sobre o Power Excel, ofereço que, se alguém na sala tiver um problema estranho com o Excel, poderá enviá-lo para obter ajuda. Foi assim que recebi esse problema de limpeza de dados. Alguém tinha uma planilha de resumo parecida com esta:

Folha de trabalho de resumo

Eles queriam reformatar os dados para ficarem assim:

Dados reformatados desejados

Uma pista interessante sobre esses dados: o 18 em G4 parece ser um subtotal de H4: K4. É tentador remover as colunas G, L e assim por diante, mas primeiro você precisa extrair o nome do funcionário de G3, L3 e assim por diante.

Eram 4 da manhã de um domingo, 9 de fevereiro, quando liguei o gravador de vídeo e gravei alguns passos desajeitados no Power Query para resolver o problema. Como era domingo, dia em que normalmente não faço vídeos, pedi que mandassem ideias de como resolver o problema. 29 soluções foram enviadas.

Cada solução oferece novas melhorias interessantes em relação ao meu processo. Meu plano é começar uma série de artigos que mostrem as várias melhorias em meu método.

Assistir vídeo

Antes de iniciar esse processo, convido você a ver minha solução:

E o código M que o Power Query gerou para mim:

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))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", ("Category Description"), "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter(("_"), QuoteStyle.Csv, false), ("Attribute.1", "Attribute.2")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Attribute.1", type text), ("Attribute.2", Int64.Type))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",(("Attribute.1", "TextValue"))), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Length", each Text.Length((TextValue))), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Name", each if (Length)> 2 then (TextValue) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Name")), #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",("Category Description", "Name", "TextValue", "Value", "Length")), #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ((TextValue) = "Q1" or (TextValue) = "Q2" or (TextValue) = "Q3" or (TextValue) = "Q4")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each (Name) "Dept. Total"), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",("Length")), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"(TextValue)), "TextValue", "Value", List.Sum), #"Sorted Rows" = Table.Sort(#"Pivoted Column",(("Name", Order.Ascending))), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Total", each (Q1)+(Q2)+(Q3)+(Q4)) in #"Added Custom1"

Antes de começarmos a encontrar as soluções, vamos abordar vários comentários comuns:

  • Alguns de vocês disseram que voltariam para trás para descobrir por que os dados estão aparecendo neste formato para começar. Agradeço esses comentários. Todo mundo que disse isso é uma pessoa melhor do que eu. Aprendi ao longo dos anos que quando você pergunta "Por quê?" a resposta geralmente passa por esse ex-funcionário que iniciou esse caminho há 17 anos e todo mundo segue assim, já que já estamos todos acostumados.
  • Além disso - muitos de vocês - disseram que a solução final deveria ser uma mesa vertical alta e então usar uma mesa dinâmica para produzir os resultados finais. Jonathan Cooper resumiu isso da melhor maneira: "Também concordo com alguns dos outros comentários do YouTube que um conjunto de dados adequado não teria" Totais "e não precisaria ser girado no final. Mas se o usuário realmente quiser um mesa velha então você dá a eles o que eles querem. " Eu posso realmente ver os dois lados disso. Adoro uma tabela dinâmica e a única coisa mais divertida do que o Power Query é o Power Query com uma bela mesa dinâmica no topo. Mas se pudermos fazer tudo no Power Query, temos menos uma coisa a quebrar.

Aqui estão os hiperlinks para várias técnicas

  • Técnicas de Power Query

    • Numeração de grupos de registros
    • Extraindo dois caracteres à esquerda
    • Coluna Total
    • Else if Clauses
    • Vários cabeçalhos idênticos no Power Query
    • O que excluir
    • Dividido por Q
    • Classificando itens de linha
    • Soluções Power Query de Excel MVPs

  • Indo além da interface do Power Query

    • Table.Split
    • O Mundo de Bill Szysz

  • Soluções de Fórmula

    • Uma fórmula de matriz dinâmica
    • Colunas de ajudantes da velha escola
    • Soluções de Fórmula

  • Composição de todas as ideias de cima e vídeo final

    • Composto das melhores ideias de todos

Artigos interessantes...