Power Query: Excluir isto, Excluir aqueles ou não excluir nada? - Dicas de Excel

Índice

Nota

Este é um de uma série de artigos detalhando soluções enviadas para o desafio Podcast 2316.

Estou me divertindo muito, examinando as 29 soluções diferentes para o desafio de limpeza de dados.

Em minha solução, e em muitas soluções, cheguei ao ponto em que queria excluir a linha do subtotal.

Precisa deletar algumas linhas

Até agora, porém, houve duas maneiras muito diferentes de lidar com isso:

Chris McNeil usou Pivot Quarters, Fill Up, e manteve apenas as linhas que excluí!
Michael Karpfen guardou tudo. Por que excluir o total de linhas e adicioná-las novamente mais tarde como uma coluna de total?

Método de Chris McNeil

Chris adicionou a coluna Índice e Módulo conforme descrito aqui.

Mas então ele usou o Módulo e girou-o para atravessar as colunas.

Método de Chris McNeil

Isso deixou um monte de valores nulos, que ele preencheu com Fill Up.

Mais tarde, ele manteve apenas os nomes dos funcionários. Na vida real, seu método não funcionará como está escrito, porque você terá nomes de funcionários reais como Andy, Bob, Charlie em vez de Employee1, Employee2, Employee3, mas há outras maneiras de identificar essas linhas para manter.

Aqui está o código de Chris

let Source = Excel.CurrentWorkbook()((Name="Table1"))(Content), #"Changed Type" = Table.TransformColumnTypes(Source,(("Category Description", type text), ("Dept. Total", type number), ("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", Int64.Type), ("Employee 1", type number), ("Q12", type number), ("Q23", type number), ("Q34", Int64.Type), ("Q45", Int64.Type), ("Employee 2", Int64.Type), ("Q16", Int64.Type), ("Q27", Int64.Type), ("Q38", Int64.Type), ("Q49", Int64.Type), ("Employee 3", Int64.Type), ("Q110", Int64.Type), ("Q211", Int64.Type), ("Q312", Int64.Type), ("Q413", Int64.Type), ("Employee 4", type number), ("Q114", type number), ("Q215", type number), ("Q316", type number), ("Q417", Int64.Type))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", ("Category Description"), "Attribute", "Value"), #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1), #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod((Index), 5), type number), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Inserted Modulo", (("Modulo", type text)), "en-US"), List.Distinct(Table.TransformColumnTypes(#"Inserted Modulo", (("Modulo", type text)), "en-US")(Modulo)), "Modulo", "Value"), #"Sorted Rows" = Table.Sort(#"Pivoted Column",(("Index", Order.Ascending))), #"Filled Up" = Table.FillUp(#"Sorted Rows",("0", "1", "2", "3", "4")), #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ((Attribute) = "Employee 1" or (Attribute) = "Employee 2" or (Attribute) = "Employee 3" or (Attribute) = "Employee 4")), #"Sorted Rows1" = Table.Sort(#"Filtered Rows",(("Attribute", Order.Ascending), ("Category Description", Order.Ascending))), #"Reordered Columns" = Table.ReorderColumns(#"Sorted Rows1",("Category Description", "Attribute", "Index", "1", "2", "3", "4", "0")), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",(("0", "Total"), ("1", "Q1"), ("2", "Q2"), ("3", "Q3"), ("4", "Q4"))), #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",("Index")) in #"Removed Columns"

Método de Michael Karpfen

Michael tinha uma coluna Modulo como muitas outras soluções. Onde a maioria das pessoas excluiria Módulo = 0, Michael o manteve, mas adicionou um rótulo de TOTAL. Se o Módulo for 0, chame-o de Total, caso contrário, use o nome da coluna Trimestre. Isso é inteligente.

Método de Michael Karpfen

Essa coluna à direita mais tarde foi girada para atravessar.

Michael então reordenou as colunas para mover os Totais para o lado direito.

Mover para o fim

Aqui está o código de Michael:

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"

Volte para a página principal do desafio Podcast 2316.

Leia o próximo artigo desta série: Power Query: Isolando as linhas que representam os trimestres.