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

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

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

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

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

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

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

Зміст

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Висновок

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

Меліса


Змініть формати дати за допомогою редактора Power Query

Змініть формати дати за допомогою редактора Power Query

У цьому посібнику ви дізнаєтеся, як перетворити текст у формат дати за допомогою редактора Power Query в LuckyTemplates.

Power Query: об’єднайте файли з кількох папок

Power Query: об’єднайте файли з кількох папок

Дізнайтеся, як об’єднати файли з кількох папок у мережі, робочому столі, OneDrive або SharePoint за допомогою Power Query.

Обчисліть місячне ковзне середнє з початку року (з початку року) у LuckyTemplates

Обчисліть місячне ковзне середнє з початку року (з початку року) у LuckyTemplates

Цей підручник пояснює, як обчислити місячне ковзне середнє на базі даних з початку року за допомогою функцій AVERAGEX, TOTALYTD та FILTER у LuckyTemplates.

Створення таблиці дат у LuckyTemplates

Створення таблиці дат у LuckyTemplates

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

Мобільні звіти LuckyTemplates. Поради та методи

Мобільні звіти LuckyTemplates. Поради та методи

У цьому короткому посібнику розповідається про функцію мобільних звітів LuckyTemplates. Я збираюся показати вам, як ви можете ефективно створювати звіти для мобільних пристроїв.

Звіти про професійну аналітику послуг у LuckyTemplates

Звіти про професійну аналітику послуг у LuckyTemplates

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

Оновлення Microsoft Power Platform | Microsoft Ignite 2021

Оновлення Microsoft Power Platform | Microsoft Ignite 2021

Ознайомтеся з основними оновленнями для Power Apps і Power Automate, а також їх перевагами та наслідками для Microsoft Power Platform.

Загальні функції SQL: огляд

Загальні функції SQL: огляд

Відкрийте для себе деякі поширені функції SQL, які ми можемо використовувати, наприклад String, Date і деякі розширені функції для обробки та маніпулювання даними.

Створення шаблону LuckyTemplates: керівництво та поради

Створення шаблону LuckyTemplates: керівництво та поради

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

Параметри полів і малі кратні в LuckyTemplates

Параметри полів і малі кратні в LuckyTemplates

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