Uwaga
Jest to jeden z serii artykułów opisujących szczegółowo rozwiązania przesłane w ramach wyzwania Podcast 2316.
W moim wyzwaniu dodatku Power Query jednym z kroków było pobranie pola nazwy z co piątego rekordu i skopiowanie go do pięciu rekordów. Moje oryginalne rozwiązanie było niezgrabne, licząc na to, że długość nazwy będzie dłuższa niż 2 znaki.
Kilka osób, w tym MF Wong, Michael Karpfen, Peter Bartholomew, Chris McNeil, Jamie Rogers, zastosowało znacznie lepsze rozwiązanie obejmujące kolumnę Index.
Podejmijmy proces, w którym dane wyglądają następująco:
Po pierwsze, MF Wong zauważył, że nie potrzebujesz pierwszych pięciu rekordów. Możesz użyć
Home, Remove Rows, Remove Top Rows… , 5 Rows.
Excel MVP Oz du Soleil z Excel on Fire również pozbył się tych pięciu, ale zrobił to, gdy były jeszcze kolumnami.
Następnie dodaj kolumnę, dodaj kolumnę indeksu, od 0. Spowoduje to wygenerowanie nowej kolumny od 0 do NN.
Po zaznaczeniu nowej kolumny Indeks przejdź do zakładki Przekształcenia i wybierz menu rozwijane Standardowe z grupy Zakładka Liczba. Uważaj: istnieje podobne menu rozwijane na karcie Dodaj kolumnę, ale wybranie tego na karcie Przekształć zapobiega dodaniu dodatkowej kolumny. Wybierz Modulo z tego menu rozwijanego, a następnie określ, że chcesz, aby reszta po podzieleniu przez 5.
Następnie
To generuje ciąg liczb od 0 do 4 powtarzanych w kółko.
Odtąd kroki, aby przenieść nazwiska pracowników, są podobne do mojego oryginalnego filmu.
Dodaj kolumnę warunkową, która przenosi nazwę lub wartość Null, a następnie Wypełnij. Więcej sposobów obliczania tej kolumny można znaleźć w dodatku Power Query: używanie klauzul Inaczej, jeśli w kolumnach warunkowych.
Wypełnij w dół, aby wypełnić nazwę od pierwszego wiersza do następnych pięciu wierszy.
Podziękowania dla MF Wong za jego film. Pamiętaj, aby włączyć CC dla napisów w języku angielskim.
https://www.youtube.com/watch?v=So1n7sLE_Mg
Wideo Petera Bartholomew:
https://www.youtube.com/watch?v=gb3OPfF_BNc
Michael Karpfen zdał sobie również sprawę, że nie ma potrzeby usuwania sum i dodawania ich później. Jego kod M to:
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"Zauważ, że Josh Johnson również użył kolumny indeksu, ale jako jednego z pierwszych kroków i użył jej jako sortowania w jednym z ostatnich kroków.
Wróć do strony głównej wyzwania Podcast 2316.
Przeczytaj następny artykuł z tej serii: Power Query: wyodrębnianie dwóch lewych znaków z kolumny.
