0: Многие пользователи до сих пор игнорируют пауэр квери, считая этот инструмент слишком сложным на самом деле power query позволяет существенно упростить работу и полностью автоматизировать рутинные операции, которые обычно приходится выполнять вручную давайте на простом примере.
1: Убедимся в преимуществах пауэр квери есть таблица продаж с 4 столбцами дата товар, количество и цена за единицу. Обратите внимание, что самой суммы продажи в таблице нет, её мы рассчитаем с помощью power query это типичный подход, когда
2: Исходных данных есть только базовая информация, а все вычисления выполняются в запросе. Итак, в 1 день было совершено несколько операций. Задача заключается в том, что необходимо получить по каждой дате список проданных товаров через запятую.
3: Пока не будем учитывать количество и цену, сначала рассмотрим самый простой вариант, который доступен каждому без каких-либо предварительных знаний о power query устанавливаем табличный курсор в любую ячейку таблицы и через вкладку данные выбираем из таблицы диапазона, так как
4: Сейчас данные не преобразованы в умную таблицу, то будет предложено это сделать в данных есть заголовки, поэтому не забываем установить соответствующую галочку откроется редактор power query сначала уберём лишние столбцы, нам нужны будут только стол.
5: С датой и товаром, удерживая нажатой клавишу ctrl указываем нужные столбцы, затем вызываем контекстное меню щелчком правой кнопки мыши и выбираем удалить другие столбцы теперь сделаем ключевой шаг группировку выделяем.
6: Столбец, дата и на вкладке преобразования. Выбираем группировать по откроется диалоговое окно, в котором будем группировать по дате. Укажем имя для нового столбца. Например, товары. Самый важный момент это выбор операции здесь
7: Нас интересуют все строки, так как нужно сохранить именно все строки внутри каждой группы нажимаем окей. Теперь в каждой ячейке столбца товары находится вложенная таблица, каждая такая таблица содержит все товары за соответствующую дату.
8: То есть нам нужно будет товары из каждой такой таблицы вывести в 1 строку через запятую и тут есть 1 нюанс, который нужно понимать, чтобы спокойно оперировать данными в power query, в отличие от эксель в power query, кроме таблиц есть ещё 2 типа да?
9: Данных список e запись таблица это двумерная структура, где каждая строка содержит несколько полей по этой причине напрямую превратить все её значения в 1 строку не получится, зато мы можем взять нужный нам столбец и превратить его значения в список.
10: Список это одномерная структура, которую пауэр квери легко обработает. Вариантов это сделать несколько, но сначала мы будем решать задачу только через интерфейс, поэтому создадим новый столбец, в котором для каждой даты будет выводиться список соответствующих товаров перехо.
11: Ходим на вкладку добавление столбца и выбираем настраиваемый столбец. Зададим для него название. Например, список товаров. Формула будет очень простой. Во первых, нам нужно будет обратиться к столбцу товары, которые содержат вложенные таблицы, поэтому выбираем его.
12: Название прямо в интерфейсе, а вот во вложенных таблицах нас будет интересовать столбец товар, поэтому также его указываем в квадратных скобках. В результате мы получим новый столбец, в котором вместо слова table, то есть таблица будет выводиться слово лист, то есть
13: Список при этом списки содержат те же самые значения, что и соответствующие столбцы во вложенных таблицах теперь нажимаем на значок с 2 стрелками в заголовке столбца из меню, выбираем извлечь значения эта опция и делает всю работу, она склеивает.
14: Списка в 1 строку через разделитель появится окно, в котором указываем разделитель. Например, запятую. Я предлагаю выбрать пользовательский вариант, чтобы задать запятую с пробелом.
15: Теперь можем удалить промежуточный столбец с таблицами и также откорректируем тип данных для столбца с датами по умолчанию. Здесь кроме даты выводится ещё и время. Теперь мы можем выгрузить данную таблицу на новый лист в эксель. Итак, мы добились желаемого.
16: Результата, но я предлагаю несколько усложнить задачу. Допустим, простого списка товаров мало, и хочется видеть ещё и количество с ценой по каждой продаже, но а также было бы неплохо рассчитать и итоговую сумму за день. Причём сам итог нужно будет именно рассчитать.
17: Ведь готового столбца с суммой в исходной таблице нет, в этот раз я предлагаю создать запрос на языке м. Это позволит действовать гибче, нежели через интерфейс редактора для удобства сначала переименуем ранее созданную умную таблицу, назовём её, например.
18: Продажи. Теперь создадим пустой запрос, для этого переходим на вкладку данные и через меню получить данные. Создаём пустой запрос откроется редактор power query с новым чистым запросом на вкладке главная. Открываем расширенный редактор давайте
19: Напишем запрос с нуля, поэтому выделим и удалим все, что сейчас здесь присутствует. Запрос начинается с ключевого слова let. Оно открывает блок с шагами. 1 шаг назовём источник в нём мы подключимся к таблице эксель с помощью функции эксель-ка.
20: Воркбук. Она возвращает список всех таблиц, которые содержит книга в фигурных скобках указываем нужную нам таблицу по её имени продаже. Именно так. Ранее мы назвали нашу таблицу. Ну а контент в квадратных скобках достаёт содержимое.
21: Этой таблицы 2 шаг группировка. Воспользуемся функцией тейбл групп. В 1 аргументе этой функции указывается предыдущий шаг запроса у нас это источник, 2 аргумент список столбцов, по которым производится группировка.
22: В фигурных скобках указываем столбец дата 3 аргумент это список того, что мы хотим получить в каждой группе ранее, когда мы производили группировку через редактор, то видели результаты преобразований на каждом шаге. По этой причине вопросов, скорее всего, не воз.
23: Никала. Однако, сейчас стоит пояснить, что означает группа. Когда мы группируем таблицу по дате, то паркери внутри себя разбивает её на подтаблицы по 1 на каждую уникальную дату. Эти подтаблицы и называются группами для решения нашей задачи.
24: Нужно будет произвести сразу 3 агрегирования. Коротко поясню, что означает данный термин. Когда мы в предыдущем примере группировали данные через интерфейс, то в диалоговом окне был пункт операция. Там можно было выбрать сумма, средняя, количество строк. И так
25: Далее вот это и есть агрегирование. То есть это способ превратить группу из нескольких строк в 1 итоговое значение. Сейчас в коде мы сделаем тоже самое, но сразу для 3 столбцов 1 агрегирование позволит нам получить простой список товара.
26: Через запятую открываем фигурные скобки и в кавычках указываем имя нового столбца, в который будут помещены агрегированные данные, например, товары, затем задаём действие, которое применяется к каждой группе. За это отвечает ключевое слово.
27: Ich когда внутри группы мы берём столбец товар, то получаем список всех товаров за определённую дату с помощью функции текст комбайн мы сможем склеить все эти данные в 1 строку через разделитель в качестве разделителя можно задать запятую с.
28: Белом в конце указываем тип данных для нового столбца. Текст. 2 аггрегирование это детализация с количеством и ценой. Имя нового столбца будет детализация, формула будет чуть сложнее, при этом мы также задействуем функцию
29: Текст комбайн. Но внутри применим ещё 1 функцию лист трансформ. Эта функция проходит по списку, и к каждому элементу этого списка применяет преобразование. Соответственно, 1 аргументом данной функции является список, который нужно подвергать преобразованиям.
30: Ну a2 аргумент это функция, которая эти преобразования и будет производить. Чтобы обрабатывать строки группы по 1, нам нужно будет сначала превратить подтаблицу в список записей для этих целей. Задействуем функцию тейбл ту рекордс символ
31: Черкивание в её аргументе означает всю текущую группу целиком. На выходе мы получим список, где каждая запись это 1 строка исходной таблицы. Теперь лист трансформ сможет пройтись по этому списку и применить нашу функцию к каждой записи по
32: Отдельности останется лишь создать саму функцию. В ней мы определим, как именно будет выглядеть строка. Сделаем так сначала пусть идёт наименование товара, поэтому указываем соответствующий столбец, затем через символ амперсант, который здесь работает.
33: Так же, как и в экселе, приклеим к названию количество его мы возьмём из соответствующего столбца, предварительно преобразовав значение в текстовое, для этого задействуем функцию текст from сделать это нужно обязательно, ведь склеивать через амперсант можно только.
34: Текстовые значения. Ну а в столбце с количеством у нас находятся числа после количества. Добавим текстом штуки и знак умножения. Ну и по аналогии приклеим цену за единицу товара из столбца. Цена в итоге для каждой
35: Продажи, получаем строку следующего вида. Сразу видно, сколько штук товара было продано и по какой цене таким образом мы обработали 1 товар. Ну а чтобы склеить все товары группы в 1 строку, возвращаемся к функции. Текст комбайн она обрамляет
36: Функцию лист трансформ снаружи зададим разделитель точку с запятой и пробел. Ну и останется указать тип столбца. Текст последнее, 3 аггрегирование это итоги за день. Имя нового столбца будет итого, поскольку
37: Готового столбца с суммами у нас нет, то необходимо будет произвести расчет самостоятельно. Снова задействуем функцию лист трансформ. С её помощью пробежимся по записям группы и для каждой продажи умножим количество на цену на вы.
38: Получим список сумм по каждой позиции этот список оборачиваем функцией лист сам, и она просуммирует все значения вместе, давая итог за день тип столбца будет числовой.
39: И произведём ещё 1 шаг. Сортировку давайте отсортируем полученную таблицу по дате, чтобы значения выводились по возрастанию для этих целей. Задействуем функцию тейбл сорт, сошлёмся на предыдущий шаг группировки, а затем укажем стол.
40: Для сортировки при такой записи он будет отсортирован по возрастанию, что, собственно, нам и нужно, ну и завершаем запрос, введя ключевое слово in, затем указываем имя последнего шага, результат которого запрос и вернёт.
41: Получаем необходимый результат. Преимущества такого подхода очевидны. Во первых, мы можем очень гибко формировать детализацию, приводя её к нужному ввиду. Во вторых, сами вычисления будут спрятаны внутри запроса. В исходной таблице достаточно будет хранить только количество
42: И цену, но, а сумма будет получаться автоматически, ну и, само собой, полученные данные можно будет выгрузить на лист эксель в виде отдельной таблицы. Запрос будет автоматически обрабатывать все изменения или новые данные, которые появятся в исходной таблице, да?
43: Безусловно, этот подход требует некоторого знания функций power query, ну и, само собой, принципов обработки данных с его помощью ну а погрузиться в эту тему можно с помощью моего курса первые 7 видео.