Nota
Questo è uno di una serie di articoli che descrivono in dettaglio le soluzioni inviate per la sfida Podcast 2316.
Nella mia sfida Power Query, uno dei passaggi era prendere il campo del nome da ogni 5 record e copiarlo nei cinque record. La mia soluzione originale era goffa, contando sul fatto che la lunghezza del nome sarebbe stata più lunga di 2 caratteri.
Diverse persone, tra cui MF Wong, Michael Karpfen, Peter Bartholomew, Chris McNeil, Jamie Rogers, hanno utilizzato una soluzione molto migliore che coinvolge una colonna Indice.
Riprendiamo il processo in cui i dati hanno questo aspetto:
Innanzitutto, MF Wong ha notato che non sono necessari i primi cinque record. Potresti usare
Home, Remove Rows, Remove Top Rows… , 5 Rows.
Anche l'MVP di Excel Oz du Soleil di Excel on Fire si è sbarazzato di quei cinque, ma lo ha fatto quando erano ancora colonne.
Quindi, Aggiungi colonna, Aggiungi colonna indice, Da 0. Ciò genera una nuova colonna da 0 a NN.
Con la nuova colonna Indice selezionata, vai alla scheda Trasforma e scegli il menu a discesa Standard dal gruppo Scheda Numero. Fai attenzione: c'è un menu a discesa simile nella scheda Aggiungi colonna, ma la selezione di quello nella scheda Trasforma impedisce l'aggiunta di una colonna aggiuntiva. Scegli Modulo da questo menu a discesa e quindi specifica che desideri il resto dopo aver diviso per 5.
Poi
Questo genera una serie di numeri da 0 a 4 ripetuti più e più volte.
Da qui, i passaggi per portare i nomi dei dipendenti sono simili al mio video originale.
Aggiungi una colonna condizionale che riporta il nome o il valore Null e quindi Fill Down. Altri modi per calcolare questa colonna sono disponibili in Power Query: utilizzo di clausole Else If nelle colonne condizionali.
Riempi in basso per inserire il nome dalla prima riga alle cinque righe successive.
Grazie a MF Wong per il suo video. Assicurati di attivare CC per i sottotitoli in inglese.
https://www.youtube.com/watch?v=So1n7sLE_Mg
Video di Peter Bartholomew:
https://www.youtube.com/watch?v=gb3OPfF_BNc
Michael Karpfen si è anche reso conto che non è necessario eliminare i totali e aggiungerli di nuovo in un secondo momento. Il suo codice 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"Nota che anche Josh Johnson ha utilizzato una colonna Indice, ma come uno dei primissimi passaggi e l'ha utilizzata come ordinamento in uno dei passaggi finali.
Torna alla pagina principale per la sfida Podcast 2316.
Leggi il prossimo articolo di questa serie: Power Query: estrazione di 2 caratteri a sinistra da una colonna.
