Nota
Este es uno de una serie de artículos que detallan las soluciones enviadas para el desafío Podcast 2316.
En mi Power Query Challenge, uno de los pasos era tomar el campo de nombre de cada quinto registro y copiarlo en los cinco registros. Mi solución original era torpe, contando con el hecho de que la longitud del nombre sería superior a 2 caracteres.
Varias personas, incluidos MF Wong, Michael Karpfen, Peter Bartholomew, Chris McNeil y Jamie Rogers, utilizaron una solución mucho mejor que incluía una columna de índice.
Retomemos el proceso donde los datos se ven así:
Primero, MF Wong señaló que no necesita los primeros cinco registros. Podrías usar
Home, Remove Rows, Remove Top Rows… , 5 Rows.
El MVP de Excel Oz du Soleil de Excel on Fire también se deshizo de esos cinco, pero lo hizo cuando aún eran columnas.
Luego, Agregar columna, Agregar columna de índice, Desde 0. Esto genera una nueva columna de 0 a NN.
Con la nueva columna de índice seleccionada, vaya a la pestaña Transformar y elija el menú desplegable Estándar del grupo de la pestaña Número. Tenga cuidado: hay un menú desplegable similar en la pestaña Agregar columna, pero seleccionar el de la pestaña Transformar evita agregar una columna adicional. Elija Módulo de este menú desplegable y luego especifique que desea el resto después de dividir por 5.
Entonces
Esto genera una serie de números del 0 al 4 repetidos una y otra vez.
Desde aquí, los pasos para traer los nombres de los empleados son similares a mi video original.
Agregue una columna condicional que muestre el nombre o el valor Nulo y luego Rellenar. Más formas de calcular esta columna se encuentran en Power Query: uso de cláusulas Else If en columnas condicionales.
Complete Down para completar el nombre desde la primera fila hasta las siguientes cinco filas.
Gracias a MF Wong por su video. Asegúrate de activar CC para los subtítulos en 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 también se dio cuenta de que no es necesario eliminar los totales y volver a agregarlos más tarde. Su código M es:
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"Tenga en cuenta que Josh Johnson también usó una columna de índice, pero como uno de los primeros pasos y la usó como una clasificación en uno de los pasos finales.
Regrese a la página principal del desafío Podcast 2316.
Lea el siguiente artículo de esta serie: Power Query: Extracción de 2 caracteres de la izquierda de una columna.
