Power Query для начинающих: шаги, которые повторятся в следующем месяце

1. Что такое Power Query на самом деле

Перед пояснением: вы и так каждый месяц чистите этот файл с формулами и копипастом. Скажите, что Power Query может дать вам такого, чего не могут дать еще двадцать формул.

Повторяемость и рекорд. Формула преобразует данные на листе, в котором она находится. Power Query находится перед листом: он считывает источник, применяет список преобразований и куда-то помещает результат. Измените исходный файл и нажмите «Обновить», и тот же список снова запустится сверху.

Из этой формы следуют три вещи, и именно они являются причиной ее изучения.

Оригинал никогда не редактируется. Каждое преобразование происходит на входе, поэтому исходный файл остается в том виде, в каком он был получен. Это отвечает правилу «никогда не убирать на месте» на постоянной основе, а не дисциплинарно.

Шаги видны и могут быть изменены. Любой желающий может открыть запрос и прочитать, что было сделано, по порядку, прописью. Попробуйте получить это из столбца вложенных формул.

Он масштабируется за пределы сетки. Механизм запросов не ограничен миллионом строк, как рабочий лист. То, что попадает на лист, должно соответствовать; то, что проходит через запрос, не проходит.

Он также уже установлен. Power Query — это группа «Получение и преобразование» на вкладке «Данные» в текущей версии Эксель. Это тот же механизм, что и для Опыт импорта Power BI, и язык M, на котором он пишет, одинаков в обоих случаях. Однократное изучение охватывает оба инструмента.

2. Внесение данных и кнопка, которую необходимо нажать

Три маршрута охватывают практически все.

Из таблицы на этом листе. Щелкните внутри данных, Данные, Из таблицы/диапазона. Если данные еще не настоящий стол, Excel предлагает сделать один, и это правильный ответ.

Из файла. Данные, Получить данные, Из файла, затем Из книги, Из текста/CSV или Из папки. Вариант с папкой недооценен: укажите каталог, и каждый файл в нем будет рассматриваться как один набор данных, и именно так двенадцать ежемесячных извлечений становятся одной таблицей.

Из базы данных. Данные, Получить данные, Из базы данных. Те же идеи подключения, что и подключение Excel к базе данных, с добавленными сверху этапами преобразования.

Независимо от маршрута, диалоговое окно предлагает предварительный просмотр с помощью двух кнопок: Нагрузка и Преобразование данных. Нажмите «Преобразовать данные». Нагрузка сбрасывает необработанное содержимое на лист и пропускает всю точку инструмента. Если вы уже нажали «Загрузить», ничего страшного: «Данные, запросы и соединения», щелкните запрос правой кнопкой мыши, выберите «Редактировать», и вы все равно окажетесь в редакторе.

3. Прикладные шаги — это продукт

Редактор имеет сетку предварительного просмотра посередине и панель справа под названием «Примененные шаги». Каждый сделанный вами щелчок добавляет в этот список строку с именем.

Source
Promoted Headers
Changed Type
Removed Columns
Filtered Rows
Trimmed Text
Renamed Columns

Четыре вещи, которые вы можете сделать с этим списком, и все четыре делают его программой, а не историей.

Нажмите на любой шаг чтобы увидеть данные такими, какими они выглядели на тот момент. Это отладчик. Если результат неправильный, нажимайте на список, пока он не пойдет не так, и вы найдете шаг.

Удалить шаг с крестом рядом с ним. Последующие шаги продолжаются с предыдущего.

Перетащите шаг чтобы изменить его порядок. Фильтрация перед тяжелым преобразованием выполняется быстрее, чем после.

Переименование шага щелкнув его правой кнопкой мыши. «Удаленные столбцы» становятся «Удалены поля только для внутреннего использования», и запрос начинает документировать себя.

Ниже каждый шаг представляет собой одну строку языка под названием M, которую вы можете увидеть с помощью Вид, Расширенный редактор. Вам не нужно писать M, чтобы использовать Power Query, и возможность прочитать его превращает загадочный шаг в очевидный, точно так же, как чтение SQL заменяет угадывание того, что делает отчет.

4. Преобразования, которые вы будете использовать каждую неделю

Из очень длинной ленты вот такие постоянно вылезают.

Из первого запроса стоит перенять две привычки. Удалить столбцы заранее, потому что все последующие процессы выполняются быстрее, а предварительный просмотр становится читабельным. И фильтровать рано по той же причине.

Одно предостережение относительно типа изменения, поскольку оно вызывает больше сбоев в обновлении, чем все остальное вместе взятое. На этом шаге записываются точные имена столбцов, к которым он применялся. Если файл в следующем месяце переименует столбец или удалит его, этот шаг завершится неудачей и вместе с ним будет выполнено все обновление. Это такая же хрупкость, как и жестко закодированный номер столбца в поиске, и при его поломке возникает такое же ощущение.

5. Отменить поворот: тот, который меняет то, что вы можете сделать

Перед пояснением: вот ежемесячный отчет в том виде, в каком его изложил бы человек. Скажите, почему сводная таблица не может суммировать данные по месяцам.

Потому что столбца месяца нет. «Месяц» здесь — это не данные, это набор заголовков столбцов, а сводная таблица может группироваться только по полю. Вы также не можете фильтровать по февралю, сравнивать месяцы в формуле или добавлять июнь, не меняя форму каждой формулы, считывающей таблицу. Макет удобен для чтения и бесполезен для анализа.

Исправление осуществляется в один клик. Выберите столбец «Регион», щелкните его правой кнопкой мыши, Отменить поворот других столбцов. Каждый месяц столбец сворачивается в два новых столбца: один содержит старый заголовок, а другой — значение.

Region   Attribute   Value
North    Jan          1120
North    Feb           425
North    Mar           440
North    May           510
South    Jan           850
South    Feb           660
South    Mar           280
South    May           880
East     Jan           660
East     Feb           680
East     Mar          1100
East     May           600
West     Jan             0
West     Feb           480
West     Mar           765
West     May           440

Четыре строки и четыре столбца месяцев превратились в шестнадцать строк, то есть четыре раза по четыре, и это умножение — ваша проверка того, что ничего не пропало. Переименуйте атрибут в «Месяц», а «Значение» в «Доход», и теперь вопрос каждого месяца становится простым: сгруппируйте по месяцам, отфильтруйте по марту, сравните регион и месяц в сводной таблице, добавьте июнь, вставив файл, а не редактируя что-либо.

Проверяйте по сумме, а не на глаз. Шестнадцать ячеек широкого отчета в сумме дают 9890, проверенных в Excel, и это число, с которым должен вернуться несведенный столбец «Доход». Поместите оба на лист и сравните их.

=SUM(wide report block)      9890
=SUM(Revenue)                must equal it
=ROWS(long table)            must equal 4 x 4

Выберите «Отменить поворот» Другой Столбцы, а не простые столбцы Отменить поворот, потому что в первом говорится «сохранить регион, свернуть все остальное», что остается верным, когда наступает новый месяц. Обычный разворот называет столбцы, которые он сворачивает, и в следующем месяце появляется столбец, о котором он никогда не слышал.

Представьте себе отчет, который вы чаще всего получаете по электронной почте. Если заголовки столбцов представляют собой вещи, а не поля, даты, регионы и названия продуктов, то ее отделяет один щелчок правой кнопкой мыши от таблицы, которую вы действительно можете использовать.

6. Добавить и объединить

Две разные работы с названиями, которые постоянно путаются.

Добавить таблицы с одинаковыми столбцами укладываются друг на друга. Двенадцать ежемесячных файлов превращаются в одну двенадцатимесячную таблицу. «Домой», «Добавить запросы», а если вы добавляете файлы из папки, соединитель «Из папки» сделает это за вас.

После этого нужно проверить арифметику: количество добавленных строк должно равняться сумме частей. Если это не так, значит, у одного файла было другое имя столбца, и его значения оказались в новом столбце, полном пробелов.

слить переносит рядом столбцы из второй таблицы, совпадающие по ключу. Главная страница, «Объединить запросы», выберите ключевой столбец в каждом, выберите тип объединения. Это та же операция, что и поиск, и та же операция, что и соединение SQL, и она имеет те же два режима сбоя: строки, которые не находят совпадений, и строки, которые находят несколько и умножаются.

Power Query здесь необычайно хорош, поскольку диалоговое окно слияния сообщает вам, сколько строк совпало до того, как вы зафиксируете данные. Прочтите это число. Если совпали 4812 из 5000 строк, 188 являются находкой, и выбранный вами тип соединения решает, исчезнут ли они или останутся видимыми.

7. Закрытие и загрузка и три пункта назначения.

«Домой», «Закрыть и загрузить» имеет раскрывающийся список, и использование раскрывающегося списка вместо кнопки стоит дополнительного щелчка. Закрыть и загрузить в предлагает три реальных варианта.

Стол. Результат отображается на листе как настоящая таблица Эксель. Используйте это, когда людям нужно просмотреть строки или построить на их основе формулы. Помните, что он все равно должен поместиться на листе.

Только создать соединение. Запрос существует и не дает ничего видимого. Это подходит для промежуточных запросов, которые существуют для объединения или добавления во что-то еще, и это позволяет сохранить размер книги и список листов.

Добавьте в модель данных. Результат попадает во внутреннюю модель данных книги, а не на лист, что необходимо для больших результатов, для связей между таблицами и для сводных таблиц, которым требуется определенное число.

Полезное значение по умолчанию: загрузите готовую вещь в таблицу и установите для каждого промежуточного запроса значение «Только создавать соединение». Рабочая книга с девятью запросами и одной видимой таблицей хорошо организована; рабочая книга с девятью листами промежуточных результатов — это то, от чего вы пытались уйти.

8. Обновление и шаг, который его нарушает

Данные, Обновить все или щелкните правой кнопкой мыши выходную таблицу и Обновить. Запрос выполняется от источника вниз на каждом этапе и перезаписывает выходные данные.

Когда он выходит из строя, это почти всегда одно из четырех событий, и на панели «Примененные шаги» указывается, что именно, поскольку сбой дает имя шагу.

Файл переместился. Шаг «Исходный код» содержит полный путь. Исправьте это в разделе «Данные», «Запросы и соединения» или, что лучше, «Настройки источника данных», чтобы путь находился в одном месте.

Столбец был переименован выше по течению. Измененный тип, удаленные столбцы и переименованные столбцы - все имена столбцов записи, и все они терпят неудачу при изменении одного из них. Это наиболее распространенная причина на расстоянии.

Пришла колонка с другим содержанием. Текстовое значение в столбце, введенное в виде числа, приводит к ошибкам в предварительном просмотре, которые представляют собой ячейки, а не остановленный запрос, поэтому обновление выглядит успешным. Найдите количество ошибок в полосе заголовка столбца.

Уровни конфиденциальности. Объединение локального файла с базой данных иногда вызывает запрос на конфиденциальность, а в автоматическом контексте вместо запроса он терпит неудачу.

Привычка, которая цепляет всех четырех: ставить счетчик строк на листе рядом с загруженной таблицей, =ROWS(QueryOutput), и взгляните на него после обновления. Запрос, который возвращает 0 строк, поскольку его фильтр больше ничему не соответствует, прекрасно обновляется.

Пограничные случаи, которые незаметно нарушают запрос

Шесть, о которых стоит знать, прежде чем они произойдут.

Пробелы и нули через разворот. Пустая ячейка и ноль — это не одно и то же входные данные, поэтому в отчете с пробелами может быть меньше строк, чем ожидалось в сумме строк, умноженных на столбцы. Каждый раз сверяйте счет с умножением; для этого и нужен чек.

Типы данных автоматически определяются при частичном предварительном просмотре. Power Query типизирует столбец, просматривая первые строки, поэтому столбец, который имеет числовое значение для двухсот строк и затем содержит значение «н/д», позже будет напечатан как число и возникнет ошибка. Устанавливайте важные типы намеренно.

Шаг «Измененный тип», на котором присваивается имя каждому столбцу. Удалите его, если он вам не нужен, или замените его типами только в важных столбцах. Это основная причина сбоя обновления после изменения исходного кода.

Фильтры записываются в виде списка значений. При установке флажков в фильтре столбцов записи «сохраняют эти конкретные значения», поэтому новое значение, которое появится в следующем месяце, автоматически исключается. Используйте условие «Текст не содержит», «Больше чем» вместо списка отметок, если значения могут измениться.

Локаль для дат и десятичных знаков. Для файла, записанного с датами первого дня или десятичными запятыми, необходимо использовать языковой стандарт в диалоговом окне «Изменить тип». Без него преобразование либо выдаст ошибку, либо, что еще хуже, завершится успешно с заменой дня и месяца.

Запрос указал на файл в папке «Загрузки». Он работает отлично, пока кто-то другой не откроет книгу. Поместите общие источники в общее место и сохраните путь в настройках источника данных, а не скрывайте его на этапе «Источник».