Nota
Este é um de uma série de artigos detalhando soluções enviadas para o desafio Podcast 2316.
Em meu problema original de modelagem de dados, encontrei um problema bem no início do processo. Os dados de entrada teriam muitas colunas com o título Q1.
Na minha solução, criei um intervalo denominado “UglyData” e importei para o Power Query. Isso levou ao resultado infeliz do Power Query renomear minhas colunas para Q1_1.
Mais tarde, depois de remover o dinamismo, tive que extrair apenas os dois caracteres restantes desses cabeçalhos.
Havia três soluções separadas para esse problema:
- Wyn Hopkins e rebaixar cabeçalhos
- MF Wong e desmarque My Table Has Headers (também sugerido por Peter Bartholomew)
- Jason M e simplesmente exclua os Cabeçalhos promovidos (também sugerido por Ondřej Malinský e o MVP do Excel John MacDougall)
A primeira inovação foi de Wyn Hopkins, da Access Analytic. Em vez de um intervalo nomeado, Wyn converteu os dados em uma tabela usando Ctrl + T. Nesse ponto, o dano aos títulos foi feito, pois o Excel converteu os títulos em:
Depois que Wyn colocou os dados no Power Query, ele abriu o menu suspenso Usar primeira linha como cabeçalhos e escolheu Usar cabeçalhos como primeira linha. Eu nunca percebi que isso estava lá. Ele cria uma etapa chamada Table.DemoteHeaders.
Mas, mesmo com a melhoria de Wyn, ele ainda teria que extrair os primeiros 2 caracteres desses cabeçalhos.
A segunda inovação é a técnica de MF Wong. Ao criar a tabela, ele desmarcou My Table Has Headers!
Isso garante que o Excel deixe os vários cabeçalhos Q1 sozinhos e não haja necessidade de extrair o sufixo extra posteriormente.
Eu entendo que há pessoas no campo “Eu amo mesas”. O vídeo de MF Wong demonstrou como ele pode adicionar novos funcionários à direita dos dados e a tabela se expande automaticamente. Existem muitas boas razões para usar tabelas.
Mas, como adoro subtotais, visualizações personalizadas e filtro por seleção, tendo a não usar tabelas. Portanto, agradeço a solução de Jason M. Ele manteve os dados como o intervalo nomeado de UglyData. Assim que importou os dados para o Power Query, ele excluiu estas duas etapas:
Agora, com os dados simplesmente na linha 1, não há problemas em ter muitas colunas chamadas Q1.
Aqui está o código de Wyn Hopkin mostrando DemotedHeaders:
let Source = Excel.CurrentWorkbook()((Name="Table1"))(Content), #"Demoted Headers1" = Table.DemoteHeaders(Source), #"Transposed Table1" = Table.Transpose(#"Demoted Headers1"), #"Added Custom" = Table.AddColumn(#"Transposed Table1", "Custom", each if Text.Start((Column1),1) = "Q" then null else (Column1)), #"Filled Down" = Table.FillDown(#"Added Custom",("Custom")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Custom) "Dept. Total")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith((Column1), "Employee")), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", (PromoteAllScalars=true)), #"Extracted First Characters" = Table.TransformColumns(#"Promoted Headers", (("Category Description", each Text.Start(_, 2), type text))), #"Reordered Columns" = Table.ReorderColumns(#"Extracted First Characters",("Category Description_1", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category Description_1", "Category Description"), "Attribute", "Value"), #"Reordered Columns1" = Table.ReorderColumns(#"Unpivoted Other Columns",("Category Description_1", "Attribute", "Category Description", "Value")), #"Pivoted Column" = Table.Pivot(#"Reordered Columns1", List.Distinct(#"Reordered Columns1"(#"Category Description")), "Category Description", "Value", List.Sum), #"Reordered Columns2" = Table.ReorderColumns(#"Pivoted Column",("Attribute", "Category Description_1", "Q1", "Q2", "Q3", "Q4")), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns2",(("Attribute", "Cat Deasc"), ("Category Description_1", "Emp Name"))), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",(("Emp Name", type text), ("Q1", Int64.Type), ("Q2", Int64.Type), ("Q3", Int64.Type), ("Q4", Int64.Type))), #"Inserted Sum" = Table.AddColumn(#"Changed Type", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), Int64.Type) in #"Inserted Sum"Volte para a página principal do desafio Podcast 2316.
Leia o próximo artigo desta série: Power Query: Excluir isto, Excluir aqueles ou excluir nada ?.
