Добавление нескольких листов в Excel в LuckyTemplates

В этом руководстве мы обсудим, как импортировать и открывать файл Excel с несколькими листами в одну таблицу LuckyTemplates.

Этот файл данных Excel будет использоваться в этом руководстве.

Файл содержит 60 листов данных с одинаковой компоновкой.

Цель этого руководства — загрузить все листы Excel в LuckyTemplates и получить информацию об облигациях в виде одной таблицы. Вам нужно получить тикер безопасности и добавить его в отдельный столбец. Затем добавьте их все вместе в одну таблицу, которая использует пользовательский интерфейс для создания нужного вам кода.

Оглавление

• Импорт листов Excel в LuckyTemplates
• Вывод тикера безопасности из Excel в LuckyTemplates
• Удаление пустых строк
• Дублирование запроса
• Назначение типов данных столбцам
• Добавление столбца тикера безопасности из Excel в LuckyTemplates
• Создание пользовательского столбца для запроса облигаций
• Заключение

Импорт листов Excel в LuckyTemplates

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

В поле «Управление параметрами» нажмите «Создать» и введите FileLocation в качестве имени параметра. Затем установите тип данных на текст и предлагаемые значения на любое значение . Скопируйте путь к файлу Excel и вставьте его в поля Текущее значение.

Нажав OK, нажмите «Новый источник» и выберите «Книга Excel» .

Затем выберите и откройте файл Excel. После этого откроется экран навигатора со списком всех листов в файле Excel.

Выберите первый лист и нажмите OK. После этого вы увидите таблицу в редакторе Power Query.

Следующее, что нужно сделать, это изменить жестко заданный путь к файлу в параметре файла. Откройте окно расширенного редактора и измените путь к файлу в исходном коде на FileLocation .

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

Вывод тикера безопасности из Excel в LuckyTemplates

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

Пользовательский интерфейс может помочь вам извлечь значение тикера. Щелкните правой кнопкой мыши ячейку и выберите «Добавить как новый запрос» .

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

С помощью этих кодов теперь вы можете извлечь значение. Вернитесь к таблице Bond и получите данные Bond. Сначала удалите верхние 8 восьми рядов. Щелкните Удалить строки и выберите Удалить верхние строки .

Затем введите 8 в поле «Количество рядов» и нажмите «ОК».

Затем нажмите «Использовать первую строку как заголовки», чтобы установить заголовки.

После этого таблица Bond будет выглядеть так.

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

Удаление пустых строк

Чтобы удалить пустые строки, нажмите «Удалить строки» и выберите «Удалить пустые строки» .

Это преобразование генерирует такой синтаксис:

Record.FieldValues ​​получает все значения из текущей строки таблицы в виде списка. List.RemoveMatchingItems удаляет все значения в первом списке, которые совпадают во втором списке. Второй список содержит только пустую текстовую строку или нуль. Это значения, которые будут исключены из первого списка.

Если все пустые текстовые строки и null были удалены из списка со значениями поля записи, список должен быть пустым, а List.IsEmpty будет оцениваться как True. Затем Table.SelectRows сохранит True.

Вы не должны получить таблицу только с пустыми строками. Вот почему перед List.IsEmpty добавляется ключевое слово not . Затем это возвращает таблицу, содержащую непустые строки.

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

После выполнения преобразования вы увидите этот синтаксис в строке формул.

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

Дублирование запроса

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

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

После этого таблица Bond будет выглядеть так.

Оттуда повторно используйте часть логики, созданной на шаге «Удаленные пустые строки», чтобы преобразовать запись в список и удалить пустые значения. Вернитесь к запросу Bond и выберите шаг «Удалить пустые строки». Затем скопируйте этот M-код.

Вернитесь к продублированному запросу и вставьте код в строку формул. Затем расположите несколько строк, чтобы формула выглядела так.

Теперь вы создали список с именами столбцов, которые хотите сохранить. Переименуйте запрос Column Names .

Затем вернитесь к запросу Bond. Поскольку вы создали запрос, содержащий все столбцы, которые вы хотите сохранить, вам нужно перечислить все столбцы, которые вы хотите исключить, в функции Table.RemoveColumns .

Измените {Column4} с тем же синтаксисом, который был скопирован из шагов удаления пустых строк. Измените также {"", null} на ColumnNames .

Затем вам нужно передать список с фактическими именами столбцов из таблицы Bond. Измените Record.FieldValues(_) на Table.ColumnNames() . Введите # «Removed Blank Rows» внутри круглых скобок, чтобы передать аргумент ссылки на таблицу.

Назначение типов данных столбцам

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

Для столбцов PX_LAST и YLD_YTM_MID выберите тип данных Decimal Number.

С помощью этих трех запросов вы создали все строительные блоки, которые можно использовать для разработки решения, которое обращается ко всем листам в файле Excel . Для этого вам нужно продублировать запрос Bond и удалить все шаги, кроме шага Source, на панели Applied Steps.

Добавление столбца тикера безопасности из Excel в LuckyTemplates

На этапе «Источник» вы можете увидеть все данные в файле Excel. Вместо того, чтобы создавать отдельный запрос для каждого листа, вы можете использовать запрос Bond и преобразовать вложенные таблицы в столбец Data.

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

Вам нужно создать логику, используя оператор , чтобы получить тикер безопасности. Перейдите к запросу Column2 и скопируйте адрес бегущей строки Security из строки формул.

Затем вернитесь к запросу Bond и добавьте настраиваемый столбец.

Назовите столбец Security Ticker и напишите следующий M-код.

В формуле есть оператор IF , говорящий, что если слово «Безопасность» найдено в столбце 1, оно предоставит значение ячейки из столбца 2, которая находится рядом с ним. В противном случае будет предоставлен нуль.

После нажатия OK в таблицу добавится новый столбец с тикерами Security.

Нажмите кнопку раскрывающегося списка в столбце «Тикер безопасности» и снимите флажок с нуля, чтобы удалить все значения NULL в столбце.

После этого у вас останется вся информация об облигациях со всех листов. Все, что вам нужно сделать, это повторить выполненные преобразования, выполненные в запросе Bond(2), и применить их к вложенным таблицам в столбце Data.

Создание пользовательского столбца для запроса облигаций

Перейдите к запросу Bond(2), откройте окно расширенного редактора и скопируйте следующий код:

Затем вернитесь к запросу Bond и создайте еще один настраиваемый столбец. Поскольку вам нужно применить несколько преобразований на нескольких этапах, вы должны использовать оператор let . Итак, введите let и вставьте код из расширенного редактора.

Затем измените Bond_Sheet на Data , чтобы преобразовать таблицу в столбец Data.

После нажатия OK в таблицу добавится новый столбец.

Если щелкнуть пробел внутри любой ячейки этого столбца, вы увидите данные из запроса Bond(2).

Все, что вам нужно, находится в последних двух столбцах запроса Bond. Итак, выберите столбцы Security Ticker и Custom и нажмите « Удалить другие столбцы» на ленте «Удалить столбцы».

Разверните столбец «Пользовательский» и снимите флажок «Использовать исходное имя как префикс».

После этого проверьте таблицу. Под таблицей выберите параметр «Профилирование столбцов на основе всего набора данных ».

После этого преобразования в столбце данных произойдет ошибка.

Поэтому изучите ошибку, щелкнув столбец «Данные» и выбрав «Сохранить ошибки» на ленте «Сохранить строки».

Если щелкнуть значение ошибки в столбце «Данные», вы сможете прочитать сообщение об ошибке ниже:

Чтобы решить проблемы, сначала удалите шаг Сохраненные ошибки. Щелкните столбец «Данные» и выберите «Удалить ошибки» на ленте «Удалить строки».

Затем снова установите параметр профилирования столбца на 1000 первых строк. Вот и все!

Заключение

Это решение на основе пользовательского интерфейса может помочь вам добавить несколько листов из файла Excel в LuckyTemplates. Вместо создания 60 отдельных запросов и многократного выполнения всех преобразований это решение позволяет создать один запрос, выполняющий все преобразования. Используйте и максимизируйте это решение, чтобы создать отличный отчет о данных .

Мелисса