Додавання кількох аркушів у Excel до LuckyTemplates

У цьому підручнику розповідається про те, як імпортувати та відкривати файл Excel із кількома аркушами в одну таблицю LuckyTemplates.

Цей файл даних Excel буде використано для цього підручника.

Файл містить 60 аркушів даних з однаковим макетом.

Мета цього підручника — завантажити всі аркуші Excel у LuckyTemplates і отримати інформацію про Bond як одну таблицю. Вам потрібно отримати тікер безпеки та додати його в окремий стовпець. Потім додайте їх усі разом в одну таблицю, яка використовує інтерфейс користувача для генерації потрібного коду.

Зміст

• Імпортування таблиць Excel до LuckyTemplates
• Перенесення тікера безпеки з Excel у LuckyTemplates
• Видалення порожніх рядків
• Дублювання запиту
• Призначення типів даних стовпцям
• Додавання стовпця Ticker безпеки з Excel до LuckyTemplates
• Створення спеціального стовпця для запиту на облігації
• Висновок

Імпортування таблиць Excel до LuckyTemplates

Перше, що потрібно зробити, це відкрити та створити параметр для збереження розташування файлу за допомогою параметра Керування параметрами .

У полі «Керування параметрами» клацніть «Новий» і введіть FileLocation як назву параметра. Далі встановіть Тип даних на Текст і Пропоновані значення на Будь-яке значення . Скопіюйте шлях до файлу Excel і вставте його в поля поточного значення.

Натиснувши OK, натисніть «Нове джерело» та виберіть «Книга Excel» .

Далі виберіть і відкрийте файл Excel. Після цього відкриється екран навігатора зі списком усіх аркушів у файлі Excel.

Виберіть перший аркуш і натисніть OK. Після цього ви побачите таблицю в редакторі Power Query.

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

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

Перенесення тікера безпеки з Excel у LuckyTemplates

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

Інтерфейс користувача може допомогти вам отримати значення тікера. Клацніть комірку правою кнопкою миші та виберіть Додати як новий запит .

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

За допомогою цих кодів тепер ви можете отримати значення. Поверніться до таблиці Bond і отримайте дані Bond. Спочатку зніміть 8 верхніх восьми рядів. Натисніть «Видалити рядки» та виберіть «Видалити верхні рядки» .

Далі введіть 8 у поле «Кількість рядків» і натисніть OK.

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

Після цього таблиця Бонда виглядатиме так.

Безпосередньо під заголовком ви побачите тонку лінію, яка вказує на якість стовпця. Звідти ви можете побачити, що в колонках досить багато пробілів. Це означає, що файл містить багато порожніх рядків.

Видалення порожніх рядків

Щоб видалити порожні рядки, натисніть «Видалити рядки» та виберіть «Видалити порожні рядки» .

Ця трансформація генерує такий синтаксис:

Record.FieldValues ​​отримує всі значення з поточного рядка таблиці у вигляді списку. List.RemoveMatchingItems видаляє всі значення в першому списку, які мають відповідність у другому списку. Другий список містить лише порожній текстовий рядок або нуль. Це значення, які будуть виключені з першого списку.

Якщо зі списку зі значеннями полів запису видалено всі порожні текстові рядки та значення null, список має бути порожнім, а List.IsEmpty оцінюватиметься як True. Тоді Table.SelectRows збереже Trues.

Ви не повинні отримати таблицю лише з порожніми рядками. Ось чому ключове слово not додається перед List.IsEmpty . Потім повертається таблиця, яка містить непорожні рядки.

Окрім порожніх рядків, вам також потрібно видалити порожні стовпці. Але перед цим подивіться, що генерує Power Query, коли ви видаляєте стовпець. Виберіть четвертий стовпець і натисніть Видалити стовпці .

Після виконання перетворення ви побачите цей синтаксис у рядку формул.

Він викликає функцію Table.RemoveColumns , потім посилається та передає попередній крок на панелі «Застосовані кроки» як перший аргумент. Далі перетворення передає список із іменами стовпців, які потрібно видалити.

Дублювання запиту

Тепер скопіюйте запит і виберіть крок «Підвищити заголовки» на панелі «Застосовані кроки». Далі клацніть правою кнопкою миші цей крок і виберіть Видалити до кінця .

Пам’ятайте, що ви можете використовувати оператор позиційного індексу, щоб передати номер рядка від нуля в наборі фігурних дужок. Тому збережіть лише перший рядок, ввівши 0 у двох фігурних дужках у рядку формул.

Після цього таблиця Бонда виглядатиме так.

Після цього повторно використовуйте частину логіки, створеної кроком «Видалені порожні рядки», щоб перетворити запис на список і видалити нуль. Поверніться до запиту Bond і виберіть крок Remove Blank Rows. Далі скопіюйте цей код M.

Поверніться до повторюваного запиту та вставте код у рядок формул. Потім упорядкуйте кілька рядків, щоб формула виглядала так.

Тепер ви створили список із назвами стовпців, які хочете зберегти. Перейменуйте запит у Назви стовпців .

Потім поверніться до запиту Bond. Оскільки ви створили запит, який містить усі стовпці, які потрібно зберегти, вам потрібно вказати всі стовпці, які потрібно виключити, у функції Table.RemoveColumns .

Змініть {Column4} з тим самим синтаксисом, який було скопійовано з кроків «Видалені порожні рядки». Змініть також {“”, null} на ColumnNames .

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

Призначення типів даних стовпцям

Наступне, що потрібно зробити, це призначити відповідні типи даних стовпцям. Для стовпця Дата клацніть піктограму біля заголовка та виберіть Дата.

Для стовпців PX_LAST і YLD_YTM_MID виберіть тип даних «Десяткове число».

За допомогою цих трьох запитів ви створили всі будівельні блоки, які можна використовувати для розробки рішення, яке стосується всіх аркушів у файлі. Для цього вам потрібно скопіювати запит Bond і видалити всі кроки, крім кроку Source, на панелі Applied Steps.

Додавання стовпця Ticker безпеки з Excel до LuckyTemplates

На кроці «Джерело» ви можете побачити всі дані у файлі Excel. Замість того, щоб створювати окремий запит для кожного аркуша, ви можете використати запит Bond і перетворити вкладені таблиці в стовпці «Дані».

Спочатку додайте тікер безпеки. Якщо клацнути білий простір у клітинці будь-якої таблиці, ви побачите попередній перегляд вмісту цієї таблиці.

Вам потрібно створити логіку за допомогою оператора, щоб отримати тікер безпеки. Перейдіть до запиту Column2 і скопіюйте адресу тікера безпеки з рядка формул.

Потім поверніться до запиту Bond і додайте спеціальний стовпець.

Назвіть стовпець Security Ticker і напишіть наступний код M.

У формулі є оператор IF , у якому сказано, що якщо слово «Безпека» знайдено в стовпці 1, воно надасть значення клітинки зі стовпця 2, яка є суміжною з ним. В іншому випадку буде надано значення null.

Після натискання ОК до таблиці буде додано новий стовпець із тикерами безпеки.

Натисніть кнопку розкривного меню стовпця Security Ticker і зніміть прапорець null, щоб видалити всі null-и в стовпці.

Після цього у вас залишиться вся інформація про облігації з усіх аркушів. Все, що вам потрібно зробити, це повторити перетворення, виконані в запиті Bond(2), і застосувати їх до вкладених таблиць у стовпці «Дані».

Створення спеціального стовпця для запиту на облігації

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

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

Потім змініть Bond_Sheet на Data , щоб перетворити таблицю в стовпець Data.

Після натискання ОК в таблицю буде додано новий стовпець.

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

Все, що вам потрібно, знаходиться в останніх двох стовпцях запиту Bond. Отже, виберіть стовпці Security Ticker і Custom і натисніть « Видалити інші стовпці» на стрічці «Видалити стовпці».

Розгорніть стовпець «Спеціальний» і зніміть прапорець «Використовувати вихідну назву як префікс».

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

Після цього перетворення в стовпці «Дані» з’явиться помилка.

Тому перевірте помилку, клацнувши стовпець «Дані» та вибравши «Зберегти помилки» на стрічці «Зберігати рядки».

Якщо ви клацнете значення помилки в стовпці даних, ви зможете прочитати повідомлення про помилку нижче:

Щоб вирішити проблеми, спершу видаліть крок із збереженням помилок. Клацніть стовпець «Дані» та виберіть «Видалити помилки» на стрічці «Видалити рядки».

Потім знову встановіть параметр профілювання стовпця на 1000 верхніх рядків. І це все!

Висновок

Це кероване інтерфейсом користувача рішення може допомогти вам додати кілька аркушів із файлу Excel до LuckyTemplates. Замість створення 60 окремих запитів і повторного виконання всіх перетворень це рішення може створити єдиний запит, який виконує всі перетворення. Використовуйте та максимізуйте це рішення, щоб .

Меліса