Примітка
Це одна із серії статей, що детально описують рішення, спрямовані на виклик Podcast 2316.
Одна з проблем мого рішення полягає в тому, що остаточна послідовність категорій не обов'язково відповідала вихідній послідовності стовпців. Я зрозумів це в самому кінці свого відео, і оскільки це не було особливо важливо, я про це не хвилювався.
Однак Джош Джонсон надіслав рішення, яке вирішило це. Коли Джош сказав, що він використовував стовпець Index, я припустив, що це було як Index and Modulo в Power Query: Кількість груп записів від 1 до 5 неодноразово. Але використання Джоша було зовсім іншим.
Примітка: MVP Excel Джон Макдугалл також використовував цей метод, але він об'єднав стовпець індексу в кінець опису категорії. Подивіться відео Джона тут: https://www.youtube.com/watch?v=Dqmb6SEJDXI та докладніше про його код читайте тут: MVP Excel атакують проблему очищення даних у Power Query.
На початку процесу, коли у Джоша було ще лише шість записів, він додав індекс, починаючи з 1. Джош клацнув у рядку формул і перейменував стовпець Індекс у Категорію.
Стовпець Категорія був новим останнім стовпцем. Він використовував Перемістити, щоб Початок перенести його першим:
Після цього відбувається багато інших кроків. Це кроки, які є інноваційними, але дотепер вони здебільшого висвітлювалися в інших статтях. Після багатьох таких кроків я починав думати, що категорії від 1 до 6 - це просто помилка. Я думав, що, можливо, Джош збирається їх видалити, не використовуючи.
Josh Unpivots, потім умовний стовпець, потім заповнення, потім поворот, додає загальну суму. Здається, він ніколи не використовує цю колонку категорії. Після багатьох кроків він тут:
Але потім на останніх кроках Джош сортує дані за іменем працівника, а потім за категорією!
На цьому етапі він може видалити стовпець Категорія. Остаточна різниця: ВОМ постає перед проектом А, як і в початкових колонках. Це приємний дотик.
Я також зазначу, що Джош надіслав відео, як він проходить ці кроки. Похвала Джошу за використання комбінацій клавіш всередині Power Query!
Ось код Джоша:
let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Promoted Headers" = Table.PromoteHeaders(Source, (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("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))), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Category", 1, 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",("Category", "Category Description", "Dept. Total", "Q1", "Q2", "Q3", "Q4", "Employee 1", "Q1_1", "Q2_2", "Q3_3", "Q4_4", "Employee 2", "Q1_5", "Q2_6", "Q3_7", "Q4_8", "Employee 3", "Q1_9", "Q2_10", "Q3_11", "Q4_12", "Employee 4", "Q1_13", "Q2_14", "Q3_15", "Q4_16")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category", "Category Description"), "Attribute", "Value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", (("Attribute", each Text.BeforeDelimiter(_, "_"), type text))), #"Added Conditional Column" = Table.AddColumn(#"Extracted Text Before Delimiter", "Employee Name", each if not Text.StartsWith((Attribute), "Q") then (Attribute) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee Name")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Attribute) = "Q1" or (Attribute) = "Q2" or (Attribute) = "Q3" or (Attribute) = "Q4") and ((Employee Name) "Dept. Total")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"(Attribute)), "Attribute", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Sorted Rows" = Table.Sort(#"Inserted Sum",(("Employee Name", Order.Ascending), ("Category", Order.Ascending))), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",("Category")) in #"Removed Columns"Поверніться на головну сторінку завдання Podcast 2316.
Прочитайте наступну статтю з цієї серії: MVP Excel атакують проблему очищення даних у Power Query.
