Запитване за захранване: Брой групи записи от 1 до 5 многократно - Съвети на Excel

Съдържание

Забележка

Това е една от поредицата статии, описващи подробно решения, изпратени за предизвикателството на Podcast 2316.

В моето Power Query Challenge една от стъпките беше да вземете полето за име от всеки 5-ти запис и да го копирате до петте записа. Оригиналното ми решение беше тромаво, разчитайки на факта, че дължината на името ще бъде по-голяма от 2 знака.

Няколко души, включително MF Wong, Michael Karpfen, Peter Bartholomew, Chris McNeil, Jamie Rogers използваха много по-добро решение, включващо колона Index.

Нека вземем процеса, при който данните изглеждат така:

Таблица с данни

Първо, MF Wong отбеляза, че не са ви необходими първите пет записа. Можете да използвате

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

Премахнете горните редове

Excel MVP Oz du Soleil от Excel on Fire също се отърва от тези пет, но той го направи, когато те все още бяха колони.

След това добавете колона, добавете индексна колона от 0. Това генерира нова колона от 0 до NN.

Индексна колона

С избраната нова колона Индекс отидете в раздела Трансформиране и изберете падащото меню Стандартно от групата Раздел Числа. Внимавайте: има подобно падащо меню в раздела Добавяне на колона, но избирането на този в раздела Трансформиране предотвратява добавянето на допълнителна колона. Изберете 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

Майкъл Карпфен също осъзна, че няма нужда да изтривате общите суми и да ги добавяте по-късно. Неговият М-код е:

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 знака от колона.

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