Power Query: повторяйте количество групп записей от 1 до 5 - Советы по Excel

Содержание

Заметка

Это одна из серии статей, в которых подробно описаны решения, присланные для задачи Podcast 2316.

В моем Power Query Challenge одним из шагов было взять поле имени из каждой пятой записи и скопировать его в пять записей. Мое первоначальное решение было неуклюжим, рассчитывая на то, что длина имени будет больше 2 символов.

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

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

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

Во-первых, М.Ф. Вонг заметил, что вам не нужны первые пять записей. Вы могли бы использовать

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

Удалить верхние строки

Excel MVP Oz du Soleil из Excel on Fire также избавился от этих пяти, но он сделал это, когда они еще были столбцами.

Затем, Добавить столбец, Добавить столбец индекса, От 0. Это создает новый столбец от 0 до NN.

Индексный столбец

Выделив новый столбец Index, перейдите на вкладку Transform и выберите раскрывающееся меню Standard в группе Number Tab. Будьте осторожны: есть аналогичный раскрывающийся список на вкладке «Добавить столбец», но выбор его на вкладке «Преобразование» предотвращает добавление дополнительного столбца. Выберите Modulo из этого раскрывающегося списка, а затем укажите, что вы хотите получить остаток после деления на 5.

По модулю

потом

Модуль

Это генерирует серию чисел от 0 до 4, повторяющуюся снова и снова.

Результат

Отсюда шаги по приведению имен сотрудников похожи на мое исходное видео.

Добавьте условный столбец, который либо выводит имя, либо значение Null, а затем Fill Down. Дополнительные способы вычисления этого столбца можно найти в Power Query: Использование предложений Else If в условных столбцах.

Добавить условный столбец

Заполните вниз, чтобы заполнить имя от первой строки до следующих пяти строк.

Спасибо MF Wong за его видео. Не забудьте включить CC для субтитров на английском языке.
https://www.youtube.com/watch?v=So1n7sLE_Mg

Видео Питера Бартоломью:
https://www.youtube.com/watch?v=gb3OPfF_BNc

Майкл Карпфен также понял, что нет необходимости удалять итоги и добавлять их позже. Его 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"

Обратите внимание, что Джош Джонсон также использовал столбец Index, но как один из самых первых шагов и использовал его как сортировку на одном из последних шагов.

Вернитесь на главную страницу испытания «Подкаст 2316».

Прочтите следующую статью этой серии: Power Query: извлечение двух левых символов из столбца.

Интересные статьи...