Excel Power Query (Содержание)

  • Введение в Power Query в Excel
  • Как использовать Power Query в Excel?

Введение в Power Query в Excel

Прежде чем приступить к анализу данных, необходимо выполнить импорт этих же данных. В Excel у нас есть обобщенный способ ручного ввода данных ячейка за ячейкой, строка за строкой и столбец за столбцом. Тем не менее, это становится трудным, когда у вас есть большие строки данных для ввода. Рассмотрим набор данных, в котором есть 100 тыс. Строк. Это будет трудоемкая работа, верно? Даже не стоит вручную вводить столько строк в Excel, сколько подвержено ошибкам. Что делать в таких случаях? Есть ли альтернатива для ручного ввода? Конечно, есть альтернатива для того же. Power Query! Хорошее слово, чтобы услышать? Еще приятнее, если вы научитесь использовать его в Excel для импорта и преобразования данных в Excel. В этой статье мы обсудим Power Query в Excel.

Мощный запрос для автоматизации процесса импорта данных

Power Query - это мощный инструмент Excel, который позволяет пользователю импортировать данные из различных источников в Excel. Эти источники включают в себя некоторые реляционные базы данных, такие как Microsoft SQL Server, Microsoft Access, Azure и т. Д., А также файлы данных, такие как Excel, Text, CSV, XML, JSON и т. Д. Power Query может помочь вам в автоматизации задачи импорта данных в Excel с минимальными усилиями, и это намного проще, чем вы можете чувствовать себя в навигации по самому Excel. В идеале его можно рассматривать как инструмент ETL, который позволяет извлекать, преобразовывать и загружать данные в Excel.

Как использовать Power Query в Excel?

Предположим, у нас есть текстовый файл в папке, которая состоит из 1000 строк данных. Мы будем использовать Power Query для импорта этого файла в Excel и попытаемся манипулировать им с помощью самого Power Query.

Шаг 1: Первым делом первым делом. Нам нужно создать соединение, чтобы мы могли извлечь данные из текстового файла в Excel. Откройте файл Excel> щелкните вкладку « Данные » на ленте Excel> щелкните раскрывающийся список « Получить данные» в разделе « Получить и преобразовать данные ».

Шаг 2: Как только вы нажимаете на раскрывающийся список «Получить данные», вы получаете несколько вариантов, из которых вы можете получить данные. Мы перейдем к опции « Из папки», которая находится в раскрывающемся списке « Из файла» . Этот параметр мы используем поверх параметра From Text / CSV, потому что он обладает большей универсальностью, чем последний.

Шаг 3: Как только вы нажмете на кнопку « Из папки», появится новое окно. Там вы должны просмотреть путь, по которому находится файл. Нажмите кнопку « Обзор…» и перейдите к пути, в котором находится файл данных.

Нажмите OK, когда закончите просмотр пути.

ПРИМЕЧАНИЕ . Путь, указанный здесь на скриншоте, - это путь, по которому я нашел файл. Когда вы будете следовать этому руководству, ваш файл данных может храниться где-то еще. Просмотрите этот путь.

Шаг 4. Откроется новое окно со списком всех файлов, присутствующих в пути, с различными атрибутами файла, такими как имя файла, тип данных, дата изменения и путь, по которому находится файл, расширение файла и т. Д.

В нижней части этого окна есть еще 4 параметра, которые читаются как Объединить, Загрузить, Преобразовать данные и Отменить соответственно.

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

Загрузка позволяет загрузить данные в виде таблицы / сводной таблицы в лист Excel независимо от фактического формата данных в исходном файле.

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

Отмена - это кнопка, которая отменяет все остальные операции в режиме Power Query.

Шаг 5: Нажмите на кнопку Transform Data и выберите файл с именем Source Data.txt, вы можете увидеть макет данных, как показано ниже:

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

Шаг 6: Теперь изменим тип данных первого столбца на «Дата». Выберите первый столбец с именем « Дата» > « Щелкните правой кнопкой мыши»> панель навигации « Изменить тип» > «Выбрать дату» в качестве параметра, чтобы представить все числовые значения в качестве дат для данного столбца.

Шаг 7: Как только вы нажмете «Дата», появится новое окно с именем « Изменить тип столбца» . Нажмите Добавить новый шаг, чтобы изменить формат столбца как Дата.

Посмотрите на столбец Дата сейчас, он должен был изменить формат с целых чисел на Даты.

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

Шаг 9: Измените имя столбца на Margin% и сформулируйте его как Margin / Sales под Custom Column, как показано ниже:

Нажмите кнопку ОК, и вы увидите столбец, добавляемый под макетом.

Шаг 10. Чтобы загрузить эти данные в Excel, перейдите к разделу « Главная»> «Закрыть и загрузить панель навигации» > «Закрыть и загрузить в…» (этот параметр позволяет загружать данные в виде таблицы, сводной таблицы, диаграммы сводной таблицы и т. Д.)

Шаг 11. В мастере импорта данных выберите макет таблицы « Существующая рабочая таблица» и нажмите « ОК» .

Вы можете увидеть таблицу данных, загруженную как показано ниже:

Вот как мы можем использовать Power Query для автоматизации задачи импорта данных в Excel. Давайте завернем вещи с некоторыми пунктами, которые будут помнить.

То, что нужно запомнить

  • Power Query - это самый простой способ автоматизировать задачу импорта данных в Excel. Это экономит рабочую силу, время, а также уменьшает человеческие ошибки.
  • Мы можем импортировать данные из разных баз данных, таких как SQL Server, сервер доступа и т. Д., А также из таких файлов, как текст, CSV, XML и т. Д.

Рекомендуемые статьи

Это руководство по Power Query в Excel. Здесь мы обсудим, как использовать Power Query в Excel и как импортировать данные из разных баз данных с примерами. Вы также можете просмотреть наши другие предлагаемые статьи -

  1. Как использовать Excel Hacks с сочетаниями клавиш
  2. Как создать модель данных в Excel?
  3. Примеры контрольного списка в Excel
  4. Коробка и усы в Excel

Категория: