Nota
Este é um de uma série de artigos detalhando soluções enviadas para o desafio Podcast 2316.
Em meu Power Query Challenge, uma das etapas foi pegar o campo de nome de cada 5º registro e copiá-lo para os cinco registros. Minha solução original era desajeitada, contando com o fato de que o comprimento do nome seria maior que 2 caracteres.
Várias pessoas, incluindo MF Wong, Michael Karpfen, Peter Bartholomew, Chris McNeil, Jamie Rogers, usaram uma solução muito melhor envolvendo uma coluna de índice.
Vamos pegar o processo em que os dados se parecem com isto:
Primeiro, MF Wong observou que você não precisa dos cinco primeiros registros. Você poderia usar
Home, Remove Rows, Remove Top Rows… , 5 Rows.
O MVP do Excel, Oz du Soleil, do Excel on Fire, também se livrou desses cinco, mas fez isso quando ainda eram colunas.
Em seguida, Adicionar coluna, Adicionar coluna de índice, de 0. Isso gera uma nova coluna de 0 a NN.
Com a nova coluna Índice selecionada, vá para a guia Transformar e escolha o menu suspenso Padrão no grupo Guia Número. Cuidado: há um menu suspenso semelhante na guia Adicionar coluna, mas selecionar aquele na guia Transformar evita adicionar uma coluna extra. Escolha Módulo neste menu suspenso e especifique que deseja o restante após dividir por 5.
Então
Isso gera uma série de números de 0 a 4 repetidos indefinidamente.
A partir daqui, as etapas para trazer os nomes dos funcionários são semelhantes ao meu vídeo original.
Adicione uma coluna condicional que traga o nome ou o valor Nulo e então Preencher. Mais maneiras de calcular essa coluna são encontradas em Power Query: Usando cláusulas Else If em colunas condicionais.
Preencha para baixo para preencher o nome da primeira linha às próximas cinco linhas.
Obrigado a MF Wong por seu vídeo. Certifique-se de ativar CC para legendas em inglês.
https://www.youtube.com/watch?v=So1n7sLE_Mg
Vídeo de Peter Bartholomew:
https://www.youtube.com/watch?v=gb3OPfF_BNc
Michael Karpfen também percebeu que não há necessidade de excluir os totais e adicioná-los novamente mais tarde. Seu código M é:
let Quelle = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Höher gestufte Header" = Table.PromoteHeaders(Quelle, (PromoteAllScalars=true)), #"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header",(("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))), #"Entpivotierte andere Spalten" = Table.UnpivotOtherColumns(#"Geänderter Typ", ("Category Description"), "Attribut", "Wert"), #"Hinzugefügter Index" = Table.AddIndexColumn(#"Entpivotierte andere Spalten", "Index", 1, 1), #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Hinzugefügter Index", "Benutzerdefiniert", each Number.Mod((Index)-1,5)), #"Hinzugefügte benutzerdefinierte Spalte1" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Benutzerdefiniert.1", each if (Benutzerdefiniert)=0 then (Attribut) else null), #"Nach unten gefüllt" = Table.FillDown(#"Hinzugefügte benutzerdefinierte Spalte1",("Benutzerdefiniert.1")), #"Neu angeordnete Spalten" = Table.ReorderColumns(#"Nach unten gefüllt",("Benutzerdefiniert.1", "Attribut", "Category Description", "Wert", "Index", "Benutzerdefiniert")), #"Geänderter Typ1" = Table.TransformColumnTypes(#"Neu angeordnete Spalten",(("Benutzerdefiniert", type text))), #"Hinzugefügte benutzerdefinierte Spalte2" = Table.AddColumn(#"Geänderter Typ1", "Benutzerdefiniert.2", each if (Benutzerdefiniert) = "0" then "TOTAL" else "Q"&(Benutzerdefiniert)), #"Neu angeordnete Spalten1" = Table.ReorderColumns(#"Hinzugefügte benutzerdefinierte Spalte2",("Attribut", "Category Description", "Benutzerdefiniert.1", "Wert", "Index", "Benutzerdefiniert", "Benutzerdefiniert.2")), #"Entfernte Spalten" = Table.RemoveColumns(#"Neu angeordnete Spalten1",("Attribut", "Index", "Benutzerdefiniert")), #"Pivotierte Spalte" = Table.Pivot(#"Entfernte Spalten", List.Distinct(#"Entfernte Spalten"(Benutzerdefiniert.2)), "Benutzerdefiniert.2", "Wert", List.Sum), #"Neu angeordnete Spalten2" = Table.ReorderColumns(#"Pivotierte Spalte",("Benutzerdefiniert.1", "Category Description", "Q1", "Q2", "Q3", "Q4", "TOTAL")), #"Sortierte Zeilen" = Table.Sort(#"Neu angeordnete Spalten2",(("Benutzerdefiniert.1", Order.Ascending))), #"Umbenannte Spalten" = Table.RenameColumns(#"Sortierte Zeilen",(("Benutzerdefiniert.1", "Employee Name"))) in #"Umbenannte Spalten"Observe que Josh Johnson também usou uma coluna Índice, mas como uma das primeiras etapas e a usou como uma classificação em uma das etapas finais.
Volte para a página principal do desafio Podcast 2316.
Leia o próximo artigo desta série: Power Query: Extraindo dois caracteres à esquerda de uma coluna.
