Пошаговое руководство по вашему первому скрипту Microsoft Power Query

Импорт данных и выполнение базовых изменений в Microsoft Power Query — это хороший способ создать сценарий и начать изучать, как он работает. Давайте пошагово перейдем к созданию сценария, внесению изменений и просмотру результатов.

Язык «М»

Прежде чем мы углубимся, нужно немного узнать о языке, на котором работает Power Query. Microsoft создала язык «M» для построения Power Query.

Если вы не программист, не беспокойтесь. Вам не нужно знать, как кодировать, используя язык «M», но полезно знать его формат, когда вы начинаете изучать Power Query

Базовый формат языка «М»

В своей основной форме язык «М» структурирован как «оператор ввода», например:

В сценарии Power Query позвольте , где вы размещаете код, который вы хотите запустить (ввод), и где вы хотите его запустить (вывод). Код, который запускается внутри оператора let может состоять из переменных, выражений и значений.

Переменные могут быть названы двумя способами. Первый со словами и без пробелов. Например: «Переменная» или «MyVariable».

Если вы хотите использовать пробел в имени вашей переменной, просто начните с # . Например: # «Моя переменная» или # «Новая переменная». Каждая из этих переменных имеет пробел в имени, но все еще допустима.

Имея это в виду, давайте начнем с нашего скрипта Power Query.

Импорт данных в Power Query

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

Цель этого руководства — взять столбец «Price» и преобразовать данные из числа в формат валюты с помощью Power Query.

Мы сохранили эту таблицу на рабочем столе. Давайте создадим новую книгу Excel под названием Fruit Data.xlsx для импорта наших данных.

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

Это откроет ваш файловый менеджер. Затем выберите книгу Fruits.xlsx для импорта и нажмите кнопку « ОК» .

Использование редактора Power Query

После того, как вы нажмете ОК, появится всплывающий навигатор Power Query для выбора данных в рабочей книге. Выберите таблицу Fruit из рабочей книги, справа появится предварительный просмотр выбранных вами данных, чтобы подтвердить ваш выбор.

Нажмите Transform Data, чтобы открыть редактор Power Query.

Просмотр скрипта

На первый взгляд, многое происходит, но давайте сосредоточимся на получении сценария.

С открытым редактором Power Query Editor на панели инструментов в разделе Home нажмите Advanced Editor, чтобы открыть скрипт. Откроется расширенный редактор, и внутри вы увидите скрипт Power Query на языке M.

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

Содержание скрипта

Прежде всего, обратите внимание, что сценарий — это формат «впуска / в», который мы перешли на язык M. Внутри «let» находятся строки кода, которые вносят ваши изменения, а код «in» — то, где изменения будут выводиться.

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

Сценарий Разбивка

Код начинается с оператора let , который запускает каждый блок кода. Первое, что сделает Power Query, — это импортирует файл в переменную с именем Source которая является просто файлом Fruit.xlsx, который мы выбрали для импорта.

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

Оттуда Power Query берет лист из Fruit.xlsx и устанавливает его в переменную с именем Fruit_Sheet .

Table.PromoteHeaders строка использует функцию Table.PromoteHeaders которая берет заголовки столбцов, которые мы использовали в Excel, и делает их заголовками в Power Query.

В следующей строке, начинающейся с #»Changed Type», Power Query меняет тип данных каждого столбца. Столбец «Fruit» заменяется на type text , «City» — это type text , а «Price» — type number . Power Query умный и пытался определить, какой тип данных хранится в таблице.

Наконец, оператор in выводит код в наш редактор, выводя его в последнюю переменную, которую он может распознать, #»Changed Type» .

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

Редактирование запроса

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

К счастью, у нас уже есть идея, как этого добиться. Напомним эту строку кода:

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

Каждому столбцу присваивается тип один за другим, поэтому давайте внесем изменения непосредственно в код. В настоящее время столбцу «Price» присвоен type number . Мы хотим, чтобы это была валюта, поэтому давайте изменим этот код на Currency.Type.

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

Лучший анализ данных в Microsoft Excel

Это было просто царапание поверхности скриптов Power Query с использованием языка M. Благодаря большей мощности для работы с Excel стало проще, чем когда-либо, преобразовать данные в сводные таблицы для использования при анализе данных. или сделать диаграмму в Excel.

Это одна из самых важных функций в Excel. Как и в настоящем языке программирования, она проверяет какое-то условие, и если оно выполняется — пишет в ячейку что-то одно, а если нет — пишет что-то другое.

Курсы Excel онлайн — пройти курс по Excel для новичков и профессионалов |

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

После того, как вы нажмете ОК, появится всплывающий навигатор Power Query для выбора данных в рабочей книге. Выберите таблицу Fruit из рабочей книги, справа появится предварительный просмотр выбранных вами данных, чтобы подтвердить ваш выбор.

• Разработка программы «Загрузка реквизитов из договоров в файл Excel» • Разработка функции «Курс ЦБ РФ» • Разработка программы «Сверка выписок» • Разработка программы «Формирование консолидированного ОДДС»

ИНДЕКС

• Каждый слушатель получит комплект презентационных материалов по теме курса и разработанные в процессе обучения программы.
• Вы научитесь общаться с программистами на понятном им языке, находить процессы, которые можно автоматизировать, и ставить технические задания на автоматизацию.
• Вы приобретёте знания и навыки, необходимые для самостоятельной автоматизации рутинных процессов в Excel.
• Вы самостоятельно разработаете на VBA Excel полноценную «боевую» программу.
• Вы получите Удостоверение о повышении квалификации в случае успешной защиты самостоятельно разработанной программы.

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