Вопрос или проблема
Я собираю данные о измеряемых частях за каждый месяц в один файл на весь год. При использовании “запроса из папки” мне удается собрать все данные вместе, отформатировать и отсортировать с одним исключением. Каждая деталь имеет версии “A” и “B”. К сожалению, из-за производственного заказа иногда деталь “B” измеряется раньше детали “A”. В этом случае я не хочу сортировать по времени, так как порядок будет, например, A,B,A,B,B,A,A,B,A,B. Я хочу, чтобы “A” деталь всегда располагалась перед “B” деталью. Части измеряются дважды в день, поэтому я не могу сортировать по дню, а затем по букве детали, потому что это приведет к тому, что будет, например, A,A,B,B,A,A,B,B. Как я могу отсортировать данные так, чтобы сначала шёл день, затем время, затем перезаписывалось время для поддержания порядка A,B,A,B?
Чтобы усложнить ситуацию, иногда сборщик данных ошибается и неверно помечает одну из деталей. В этом случае она отсортируется как, например, A,B,A,B,A,A,A,B,A,B. Как я могу найти эту ошибку и автоматически исправить её перед вставкой свернутых данных в таблицу?
(Данные были сильно упрощены по соображениям конфиденциальности)
Вы можете увидеть, что 2 мая утром A/B перепутаны, потому что данные B были собраны до данных A. Сортировка данных по времени испортила порядок.
Вы можете увидеть, что 2 апреля утром (1PM – это утренняя смена) есть две детали A, когда одна из них должна быть B (для этой ошибки мы можем предположить, что они были собраны в порядке “A” перед “B”, так что время сбора данных имеет значение).
Я новичок в использовании запросов и, честно говоря, сильно затрудняюсь с этой задачей. Пожалуйста, помогите мне не только решить эту проблему, но и понять её.
Вот текстовые версии данных:
Апр
Дата Время Буква Данные
4/1/2024 7:25:08 AM A 0.7
4/1/2024 7:30:56 AM B 0.5
4/1/2024 8:32:51 PM A 0.6
4/1/2024 8:36:44 PM B 0.5
4/2/2024 1:32:59 PM A 1
4/2/2024 1:38:36 PM A 0.5
4/2/2024 8:46:11 PM A 0.7
4/2/2024 8:51:31 PM B 0.7
Май
Дата Время Буква Данные
5/1/2024 1:35:12 PM A 0.6
5/1/2024 1:39:05 PM B 0.4
5/1/2024 6:07:11 PM A 0.8
5/1/2024 6:10:43 PM B 0.5
5/2/2024 10:59:32 AM A 0.8
5/2/2024 8:42:16 AM B 0.1
5/2/2024 6:15:07 PM A 0.4
5/2/2024 6:18:40 PM B 0.2
YTD
Дата Время Буква Данные
4/1/2024 7:25:08 AM A 0.7
4/1/2024 7:30:56 AM B 0.5
4/1/2024 8:32:51 PM A 0.6
4/1/2024 8:36:44 PM B 0.5
4/2/2024 1:32:59 PM A 1
4/2/2024 1:38:36 PM A 0.5
4/2/2024 8:46:11 PM A 0.7
4/2/2024 8:51:31 PM B 0.7
5/1/2024 1:35:12 PM A 0.6
5/1/2024 1:39:05 PM B 0.4
5/1/2024 6:07:11 PM A 0.8
5/1/2024 6:10:43 PM B 0.5
5/2/2024 8:42:16 AM B 0.1
5/2/2024 10:59:32 AM A 0.8
5/2/2024 6:15:07 PM A 0.4
5/2/2024 6:18:40 PM B 0.2
Я использовал простой запрос, как показано ниже, и дополнительно, по порядку, изменил все типы данных на правильные, отсортировал по дате, отсортировал по времени, удалил имя источника и удалил дубликаты.
Причина, по которой я не могу полагаться на имена файлов для сортировки данных и сохранения их так, как они есть в файле, заключается в том, что я вытаскиваю данные из листов, которые все имеют одинаковое имя, но находятся в своих собственных ежемесячных папках. Папки сортируются по алфавиту, поэтому порядок месяцев будет неправильным, если я не отсортирую его вручную.
Ответ или решение
Чтобы решить вашу проблему с сортировкой данных при консолидации месячных данных в файл за весь год, необходимо учесть несколько ключевых аспектов. Мы рассмотрим последовательность сортировки данных, учтём правила для факторов "A" и "B", а также автоматизацию проверки на ошибки в метках данных.
Определение структуры данных
Прежде всего, необходимо организовать структуру ваших данных. Имеется три основных поля: "Дата", "Время", "Буква", и "Данные". Эти поля должны быть в правильном формате, чтобы обеспечить корректную обработку.
Сортировка данных
Сортировка данных должна следовать строго определённой последовательности: по дате, затем по времени и, наконец, с учётом специального правила для "A" и "B". Чтобы реализовать это в Power Query, выполните следующие шаги:
-
Импорт данных: Используйте функцию "Запрос из папки" для объединения данных из всех файлов. Убедитесь, что все файлы имеют одинаковую структуру.
-
Изменение типов данных: Убедитесь, что столбцы "Дата" и "Время" имеют соответствующие типы данных. Преобразуйте поле "Буква" в текстовый формат.
-
Сортировка: Следующий шаг — сортировка.
- Сначала отсортируйте данные по "Дата".
- Затем отсортируйте по "Время".
- После этого добавьте ещё один шаг для сортировки, который будет давать приоритет строкам с буквой "A" перед строками с буквой "B".
В Power Query вы можете использовать следующий код для обработки логики сортировки:
let
Source = Folder.Files("<путь к вашей папке>"),
// Получаем таблицу из файлов
Data = ... , // Здесь должен быть ваш код для извлечения данных
ChangedType = Table.TransformColumnTypes(Data, {{"Date", type date}, {"Time", type time}, {"Letter", type text}, {"Data", type number}}),
// Первоначальная сортировка по Дате и Времени
Sorted = Table.Sort(ChangedType, {{"Date", Order.Ascending}, {"Time", Order.Ascending}}),
// Логика для сортировки по "Letter"
Adjusted = Table.AddColumn(Sorted, "SortOrder", each if [Letter] = "A" then 0 else 1),
FinalSorted = Table.Sort(Adjusted, {{"Date", Order.Ascending}, {"Time", Order.Ascending}, {"SortOrder", Order.Ascending}})
in
FinalSortedКоррекция ошибок меток
Чтобы автоматизировать проверку ошибок меток, можно использовать следующий подход:
- Группировка данных: Создайте таблицы группировки по "Дата" и "Время" и проверьте, что каждая группа содержит ровно одну "A" и одну "B".
- Автоматическая проверка: Если в группе обнаруживается больше одной "A" или "B", выдайте предупреждение или отметьте ошибку в отдельном столбце.
Пример кода для проверки ошибок:
let
GroupedData = Table.Group(FinalSorted, {"Date", "Time"}, {{"AllData", each _, type table [Date=nullable date, Time=nullable time, Letter=nullable text, Data=nullable number]}}),
ErrorsDetected = Table.AddColumn(GroupedData, "Error", each if List.Count(List.Select([AllData][Letter], (x) => x = "A")) > 1 then "Multiple A's" else if List.Count(List.Select([AllData][Letter], (x) => x = "B")) > 1 then "Multiple B's" else null),
FinalTable = Table.SelectRows(ErrorsDetected, each [Error] = null)
in
FinalTableЗаключение
Эти шаги помогут вам не только правильно отсортировать ваши данные, но и найти ошибки в метках перед их вставкой в итоговую таблицу. Несмотря на то, что программные операции могут показаться сложными, понимание логики и структуры ваших данных поможет устранить любую путаницу. При последующем использовании Power Query для обработки данных попробуйте детально проанализировать каждый шаг и логику работы функций, чтобы развивать навыки их применения.