Bemærk
Dette er en af en række artikler, der beskriver løsninger sendt til Podcast 2316-udfordringen.
I min Power Query Challenge var et af trinene at tage navnefeltet fra hver 5. post og kopiere det ned til de fem poster. Min oprindelige løsning var klodset og regnede med, at navnets længde ville være længere end 2 tegn.
Flere mennesker, herunder MF Wong, Michael Karpfen, Peter Bartholomew, Chris McNeil, Jamie Rogers brugte en meget bedre løsning med en indeks-kolonne.
Lad os hente processen, hvor dataene ser sådan ud:
For det første bemærkede MF Wong, at du ikke har brug for de første fem poster. Du kan bruge
Home, Remove Rows, Remove Top Rows… , 5 Rows.
Excel MVP Oz du Soleil fra Excel on Fire slap også af med disse fem, men han gjorde det, da de stadig var kolonner.
Tilføj derefter kolonne, tilføj indekskolonne, fra 0. Dette genererer en ny kolonne på 0 gennem NN.
Når den nye indeks-kolonne er valgt, skal du gå til fanen Transform og vælge rullemenuen Standard fra gruppen Tal-fane. Vær forsigtig: der er en lignende rulleliste på fanen Tilføj kolonne, men at vælge den på fanen Transform forhindrer tilføjelse af en ekstra kolonne. Vælg Modulo fra denne rullemenu, og angiv derefter, at du vil have resten efter at have divideret med 5.
Derefter
Dette genererer en række numre fra 0 til 4 gentaget igen og igen.
Herfra ligner trinene til at bringe medarbejdernavne over min originale video.
Tilføj en betinget kolonne, der enten bringer navnet eller værdien Null og derefter Udfyld. Flere måder at beregne denne kolonne findes i Power Query: Brug af andre hvis klausuler i betingede kolonner.
Fyld ned for at udfylde navnet fra den første række til de næste fem rækker.
Tak til MF Wong for hans video. Sørg for at aktivere CC for engelsk billedtekst.
https://www.youtube.com/watch?v=So1n7sLE_Mg
Peter Bartholomews video:
https://www.youtube.com/watch?v=gb3OPfF_BNc
Michael Karpfen indså også, at der ikke er behov for at slette totalerne og tilføje dem senere. Hans M-kode er:
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"Bemærk, at Josh Johnson også brugte en indeks-kolonne, men som et af de allerførste trin og brugte det som en slags i et af de sidste trin.
Gå tilbage til hovedsiden for Podcast 2316-udfordringen.
Læs den næste artikel i denne serie: Power Query: Extracting Left 2 Characters From a Column.
