Иногда данные выгружаются совсем не в том виде, в котором хотелось бы их получить.
Например, вместо привычной таблицы:
Например, вместо привычной таблицы:
И если строк несколько десятков, такую таблицу ещё можно попытаться разобрать вручную.
Но если выгрузка содержит тысячи строк, ручная обработка уже превращается в отдельную задачу.
Разберём, как можно привести такие данные в нормальный табличный вид с помощью Power Query. А заодно посмотрим, как ИИ может ускорить решение подобных нестандартных задач.
Но если выгрузка содержит тысячи строк, ручная обработка уже превращается в отдельную задачу.
Разберём, как можно привести такие данные в нормальный табличный вид с помощью Power Query. А заодно посмотрим, как ИИ может ускорить решение подобных нестандартных задач.
Что не так с исходной выгрузкой
В нашем примере информация расположена блоками по четыре строки:
Затем начинается следующий блок.
Главная проблема в том, что для анализа нам нужны не четыре строки, а одна строка на одну операцию или объект наблюдения.
То есть из этого:
Москва
Магазин 1
01.09.2026
125 000 руб
нужно получить:
- регион
- магазин
- дата продажи
- сумма продаж
Затем начинается следующий блок.
Главная проблема в том, что для анализа нам нужны не четыре строки, а одна строка на одну операцию или объект наблюдения.
То есть из этого:
Москва
Магазин 1
01.09.2026
125 000 руб
нужно получить:
И повторить такое преобразование для всей выгрузки.
Можно ли сделать это руками?
Конечно.
Если вы хорошо знаете Power Query, задача сама по себе не является какой-то невероятно сложной.
Можно добавить индекс, определить номер каждой строки внутри блока, разбить данные на группы, развернуть их в столбцы и привести типы данных.
Но здесь появляется другой вопрос:
обязательно ли каждый раз самостоятельно придумывать все шаги и писать код с нуля?
И вот здесь сегодня очень удобно подключать ИИ.
Если вы хорошо знаете Power Query, задача сама по себе не является какой-то невероятно сложной.
Можно добавить индекс, определить номер каждой строки внутри блока, разбить данные на группы, развернуть их в столбцы и привести типы данных.
Но здесь появляется другой вопрос:
обязательно ли каждый раз самостоятельно придумывать все шаги и писать код с нуля?
И вот здесь сегодня очень удобно подключать ИИ.
Как я поставила задачу ИИ
Вместо того чтобы самой писать M-код, можно подробно описать структуру данных и результат, который нам нужен.
Например, промпт может выглядеть так:
«У меня есть таблица Excel RawVerticalData с одним столбцом “Данные”.
Информация расположена повторяющимися блоками по 4 строки:
1-я строка — регион
2-я строка — магазин
3-я строка — дата продажи
4-я строка — продажи
Нужно написать M-код для Power Query, который преобразует эти данные в таблицу со столбцами:
Каждые четыре строки исходной таблицы должны превращаться в одну строку итоговой таблицы.
Поле “Дата продажи” необходимо привести к типу Date.
Из поля “Продажи” нужно удалить текст “руб” и пробелы и преобразовать значение в число.
Напиши готовый M-код и кратко объясни логику решения».
Обратите внимание на важный момент: чем точнее мы описываем структуру исходных данных и ожидаемый результат, тем выше вероятность сразу получить рабочее решение.
Фраза «сделай мне нормально эту таблицу» даст ИИ значительно меньше информации.
Например, промпт может выглядеть так:
«У меня есть таблица Excel RawVerticalData с одним столбцом “Данные”.
Информация расположена повторяющимися блоками по 4 строки:
1-я строка — регион
2-я строка — магазин
3-я строка — дата продажи
4-я строка — продажи
Нужно написать M-код для Power Query, который преобразует эти данные в таблицу со столбцами:
- Регион
- Магазин
- Дата продажи
- Продажи
Каждые четыре строки исходной таблицы должны превращаться в одну строку итоговой таблицы.
Поле “Дата продажи” необходимо привести к типу Date.
Из поля “Продажи” нужно удалить текст “руб” и пробелы и преобразовать значение в число.
Напиши готовый M-код и кратко объясни логику решения».
Обратите внимание на важный момент: чем точнее мы описываем структуру исходных данных и ожидаемый результат, тем выше вероятность сразу получить рабочее решение.
Фраза «сделай мне нормально эту таблицу» даст ИИ значительно меньше информации.
Как выглядит логика решения
В данном случае алгоритм достаточно понятный.
Сначала Power Query добавляет к каждой строке индекс:
0
1
2
3
4
5
6
7
…
Затем каждые четыре строки объединяются в одну группу.
У строк 0–3 номер группы будет 0
У строк 4–7 — группа 1
У строк 8–11 — группа 2
И так далее.
После этого по остатку от деления индекса на 4 определяется, что находится в каждой строке:
0 → Регион
1 → Магазин
2 → Дата продажи
3 → Продажи
После этого данные можно развернуть в четыре отдельных столбца.
Сначала Power Query добавляет к каждой строке индекс:
0
1
2
3
4
5
6
7
…
Затем каждые четыре строки объединяются в одну группу.
У строк 0–3 номер группы будет 0
У строк 4–7 — группа 1
У строк 8–11 — группа 2
И так далее.
После этого по остатку от деления индекса на 4 определяется, что находится в каждой строке:
0 → Регион
1 → Магазин
2 → Дата продажи
3 → Продажи
После этого данные можно развернуть в четыре отдельных столбца.
Пример M-кода
Для исходной таблицы RawVerticalData решение может выглядеть примерно так:
let
Source = Excel.CurrentWorkbook(){[Name="RawVerticalData"]}[Content],
AddIndex = Table.AddIndexColumn(
Source,
"Index",
0,
1,
Int64.Type
AddGroup = Table.AddColumn(
AddIndex,
"Group",
each Number.IntegerDivide([Index], 4),
Int64.Type
AddField = Table.AddColumn(
AddGroup,
"Field",
each {"Регион", "Магазин", "Дата продажи", "Продажи"}{Number.Mod([Index], 4)}
RemoveIndex = Table.RemoveColumns(
AddField,
{"Index"}
PivotData = Table.Pivot(
RemoveIndex,
{"Регион", "Магазин", "Дата продажи", "Продажи"},
"Field",
"Данные",
List.First
CleanSales = Table.TransformColumns(
PivotData,
{
{
"Продажи",
each
Number.FromText(
Text.Replace(
Text.Replace(
Text.Replace(Text.From(_), "руб", ""),
" ",
""
),
Character.FromNumber(160),
""
),
"ru-RU"
),
type number
}
}
ChangeDateType = Table.TransformColumns(
CleanSales,
{
{
"Дата продажи",
each Date.From(_, "ru-RU"),
type date
}
}
RemoveGroup = Table.RemoveColumns(
ChangeDateType,
{"Group"}
)
in
RemoveGroup
Код можно адаптировать под конкретный источник данных, названия столбцов и формат исходной выгрузки.
Значит ли это, что Power Query теперь можно не изучать
Нет.
И вот здесь, на мой взгляд, находится самое интересное изменение, которое происходит сейчас в работе с Excel и аналитикой.
Раньше для решения нестандартной задачи специалисту часто приходилось:
Сегодня ИИ может снять значительную часть этого барьера.
Вы можете объяснить ему задачу обычными словами и получить вариант решения за несколько секунд.
Но дальше всё равно нужно понимать:
То есть ценность специалиста постепенно смещается.
Всё меньше ценится способность помнить каждый синтаксический нюанс.
И всё больше - способность правильно поставить задачу, понять предложенное решение, проверить результат и встроить его в рабочий процесс.
И вот здесь, на мой взгляд, находится самое интересное изменение, которое происходит сейчас в работе с Excel и аналитикой.
Раньше для решения нестандартной задачи специалисту часто приходилось:
- искать похожий пример
- читать форумы
- разбираться в синтаксисе M
- самостоятельно собирать код
Сегодня ИИ может снять значительную часть этого барьера.
Вы можете объяснить ему задачу обычными словами и получить вариант решения за несколько секунд.
Но дальше всё равно нужно понимать:
- что именно сделал код
- соответствует ли результат вашей задаче
- правильно ли определились типы данных
- не потерялись ли строки
- что произойдёт при изменении структуры исходной выгрузки
- почему запрос выдал ошибку
- как объяснить ИИ, что именно нужно исправить
То есть ценность специалиста постепенно смещается.
Всё меньше ценится способность помнить каждый синтаксический нюанс.
И всё больше - способность правильно поставить задачу, понять предложенное решение, проверить результат и встроить его в рабочий процесс.
Что обязательно проверить после кода от ИИ
Даже если запрос отработал без ошибок, я рекомендую проверить несколько вещей.
1. Количество строк
Если в исходных данных было 4 000 строк, после преобразования блоков по четыре мы ожидаем получить примерно 1 000 строк.
Если получилось 987 или 1 043 — стоит разобраться почему.
2. Даты
Убедитесь, что дата действительно стала датой, а не осталась текстом.
Это особенно важно, если исходные данные могут содержать разные региональные форматы.
3. Продажи
Проверьте несколько исходных значений вручную.
Например:
125 000 руб
должно превратиться именно в число:
125000
а не в текст.
4. Структуру блоков
Наше решение предполагает, что каждый блок всегда состоит ровно из четырёх строк.
Если в какой-то момент источник добавит пятую строку, например «Менеджер», весь алгоритм необходимо будет изменить.
Именно поэтому важно понимать логику запроса, а не просто копировать код.
1. Количество строк
Если в исходных данных было 4 000 строк, после преобразования блоков по четыре мы ожидаем получить примерно 1 000 строк.
Если получилось 987 или 1 043 — стоит разобраться почему.
2. Даты
Убедитесь, что дата действительно стала датой, а не осталась текстом.
Это особенно важно, если исходные данные могут содержать разные региональные форматы.
3. Продажи
Проверьте несколько исходных значений вручную.
Например:
125 000 руб
должно превратиться именно в число:
125000
а не в текст.
4. Структуру блоков
Наше решение предполагает, что каждый блок всегда состоит ровно из четырёх строк.
Если в какой-то момент источник добавит пятую строку, например «Менеджер», весь алгоритм необходимо будет изменить.
Именно поэтому важно понимать логику запроса, а не просто копировать код.
Где особенно полезен такой подход
Подобные задачи постоянно встречаются в реальной работе.
Это могут быть:
Именно в таких случаях Power Query особенно полезен.
Вместо того чтобы каждый месяц заново приводить файл в порядок, мы один раз настраиваем последовательность преобразований, а при следующей выгрузке просто обновляем запрос.
Это могут быть:
- нестандартные выгрузки из учётных систем
- файлы из 1С
- отчёты поставщиков
- данные маркетплейсов
- выгрузки, подготовленные для чтения человеком, а не для анализа
- файлы с многоуровневыми заголовками
- таблицы с промежуточными итогами
- несколько характеристик внутри одного столбца
Именно в таких случаях Power Query особенно полезен.
Вместо того чтобы каждый месяц заново приводить файл в порядок, мы один раз настраиваем последовательность преобразований, а при следующей выгрузке просто обновляем запрос.
Главное
ИИ действительно сделал работу с Power Query проще.
Теперь необязательно уметь самостоятельно написать сложный M-код с чистого листа, чтобы попробовать решить нестандартную задачу.
Но чем лучше вы понимаете сам Power Query, структуру данных и логику преобразований, тем полезнее становится ИИ.
Потому что тогда он не заменяет ваши знания, а ускоряет работу с ними.
И это, на мой взгляд, один из самых полезных сценариев применения ИИ в Excel сегодня.
Если вы хотите научиться не только обрабатывать отдельные выгрузки, но и строить автоматизированные процессы подготовки данных и отчётности, эти задачи подробно разбираем на программе Power Excel.
Теперь необязательно уметь самостоятельно написать сложный M-код с чистого листа, чтобы попробовать решить нестандартную задачу.
Но чем лучше вы понимаете сам Power Query, структуру данных и логику преобразований, тем полезнее становится ИИ.
Потому что тогда он не заменяет ваши знания, а ускоряет работу с ними.
И это, на мой взгляд, один из самых полезных сценариев применения ИИ в Excel сегодня.
Если вы хотите научиться не только обрабатывать отдельные выгрузки, но и строить автоматизированные процессы подготовки данных и отчётности, эти задачи подробно разбираем на программе Power Excel.