Power Query: Кількість груп записів від 1 до 5 неодноразово - Поради Excel

Зміст

Примітка

Це одна із серії статей, що детально описують рішення, спрямовані на виклик Podcast 2316.

У моєму Power Query Challenge одним із кроків було взяти поле імені з кожного 5-го запису та скопіювати його до п’яти записів. Моє оригінальне рішення було незграбним, розраховуючи на те, що довжина імені буде більше 2 символів.

Кілька людей, у тому числі М.Ф. Вонг, Майкл Карпфен, Пітер Варфоломій, Кріс МакНейл, Джеймі Роджерс, використовували набагато кращі рішення із використанням стовпця "Індекс".

Давайте підберемо процес, де дані виглядають так:

Таблиця даних

По-перше, М. Ф. Вонг зазначив, що вам не потрібні перші п’ять записів. Ви могли б використовувати

Home, Remove Rows, Remove Top Rows… , 5 Rows.

Зніміть верхні ряди

Excel MVP Oz du Soleil з Excel on Fire також позбувся цих п’яти, але він зробив це, коли вони все ще були стовпцями.

Потім, Додати стовпець, Додати стовпець індексу, Від 0. Це генерує новий стовпець від 0 до NN.

Стовпець покажчика

Вибравши новий стовпець "Індекс", перейдіть на вкладку "Трансформація" та виберіть спадне меню "Стандартне" з групи "Вкладка чисел". Будьте обережні: подібний випадаючий список є на вкладці Додати стовпець, але вибір такого на вкладці Трансформація заважає додавати додатковий стовпець. Виберіть Modulo із цього спадного меню, а потім вкажіть, що ви хочете залишок після ділення на 5.

За модулем

Тоді

Модуль

Це генерує ряд чисел від 0 до 4, що повторюються знову і знову.

Результат

Звідси кроки щодо переведення імен співробітників схожі на моє оригінальне відео.

Додайте умовний стовпець, який передає ім’я або значення Null, а потім заповніть. Інші способи обчислення цього стовпця знайдено в Power Query: Використання інших речень If у умовних стовпцях.

Додайте умовний стовпець

Заповніть, щоб заповнити ім’я з першого рядка до наступних п’яти рядків.

Дякуємо М.Ф. Вонгу за його відео. Обов’язково ввімкніть CC для титрів англійською мовою.
https://www.youtube.com/watch?v=So1n7sLE_Mg

Відео Пітера Варфоломія:
https://www.youtube.com/watch?v=gb3OPfF_BNc

Майкл Карпфен також зрозумів, що немає потреби видаляти підсумки та додавати їх пізніше. Його М-код:

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"

Зверніть увагу, що Джош Джонсон також використовував стовпець "Індекс", але як один із перших кроків і використовував його як сортування на одному з останніх кроків.

Поверніться на головну сторінку завдання Podcast 2316.

Прочитайте наступну статтю з цієї серії: Power Query: Вилучення лівих 2 символів із стовпця.

Цікаві статті...