Форматування даних у LuckyTemplates: усунення нестандартних форматів даних

У сьогоднішньому дописі блогу ми обговоримо Проблему тижня №6 . Я покажу вам, як форматувати дані в LuckyTemplates за допомогою Power query . На знімку екрана нижче показано рішення, яке ви повинні мати після того, як ви виконали всі перетворення даних. Фактичний баланс становив 685 616,33 доларів США . Ви можете переглянути повне відео цього підручника внизу цього блогу.

Ми взяли багато учасників у Челлендж №6. Я ціную зусилля кожного в спробі досягти цього. Єдине, що слід зазначити, це те, що ми побачили купу з��писів, баланс яких був незначним. Я покажу вам, переглядаючи свою заявку на це змагання, чому ця сума в доларах була зниженою.

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

Я відкрию Power Query і крок за кроком поясню, як я прийшов до свого рішення. Ось як виглядає готовий продукт.

Але якщо я натисну на джерело, ви побачите, що це те, що спочатку з’явилося, коли ми під’єдналися до розташування файлу, де ми зберігали файл CSV.

Зміст

• Переваги коментарів
• Причини неправильної суми
• Фільтрування стовпців
• Створення колонки віку
• Висновок

Переваги коментарів

Подивіться на сходинки з правого боку. Ви можете побачити малу букву «i», що означає інформацію. Це означає, що в розширеному редакторі є коментарі.

Давайте швидко відкриємо розширений редактор тут. Ви бачите, що тексти, виділені зеленим кольором, є коментарями, які допомагають мені нагадати собі, про що я думав у той час.

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

Ми можемо переглянути коментарі збоку тут. Перший коментар до Changed Type1 в основному стосується зміни типів для різних стовпців. Як ви можете бачити, мої коментарі: «Переконайтеся, що поточний стовпець налаштований як десятковий, інакше тут усе почне йти погано!»

Це підводить мене до того, чому люди придумали ту неправильну суму балансу, яку я показав вам раніше.

Причини неправильної суми

Дозвольте мені показати вам інший файл PBIX, який я скопіював від когось, хто мав один із цих записів. $685 615,28 — це неправильна сума, і було багато записів, які містили цю підсумкову суму.

Давайте розберемося, як і чому так сталося. Якщо ми перейдемо на вкладку «Перетворення» та натиснемо «Визначити тип даних» , який автоматично визначить усі рядки, ми побачимо, що поточний стовпець відображається як ціле число 64, а це не те, що ми хочемо бачити.

Ось чому суми неправильні. Існує ще один спосіб, за допомогою якого ця помилка може відображатися у ваших налаштуваннях. Давайте перейдемо до Файл>Параметри та налаштування>Параметри.

У області GLOBAL і розділі Data Load ми можемо побачити Type Detection . Я вибрав «Визначати типи стовпців і заголовки для неструктурованих джерел відповідно до налаштувань кожного файлу ».

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

Однією з наших, хто дуже допоміг, коли справа доходить до коду M або мови потужних запитів, є Мелісса. Вона показала мені документ Microsoft , який пояснює нам, чому сталася ця помилка.

Як бачите, це раніше називалося Параметри проекту . Позначено параметр Автоматично визначати типи стовпців і заголовки для неструктурованих джерел .

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

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

Давайте повернемося до power query і почнемо форматувати дані в LuckyTemplates. Як ви можете бачити на кроці Changed Type , це ціле число 64 із поточним.

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

Коли я вибираю певний номер рахунку-фактури, ми бачимо, що поточна сума становить 4741,01.

Коли я клацну цей тип зміни, ви побачите, що значення змінилося та округлилося до 4741.

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

Давайте повернемося до мого рішення в power query M. Наступним кроком є ​​те, де я відфільтрував рядки, щоб показати лише порожні рядки.

Наступним кроком є ​​те, де я видалив стовпці. Я видалив усі стовпці, крім тих, які ви бачите на скріншотах. Також зауважте, що номер запису, який спочатку з’явився, насправді є номером рахунку-фактури.

Наступним кроком є ​​видалення порожніх рядків, які були нульовими для кожного поля.

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

Наступним кроком була заміна всіх значень пробілу на нуль у стовпці «Клієнт». Причина, чому ми повинні це зробити, полягає в наступному кроці.

Щоб заповнити всі ці рядки, жоден із них не може бути порожнім і має бути нульовим.

Щоб заповнити вниз, клацніть стовпець, перейдіть до Заповнити , а потім Вниз .

Ось як ви можете побачити, як усі імена йдуть у цьому стовпці.

Фільтрування стовпців

Я також відфільтрував кілька рядків тут і відфільтрував за номером рахунка-фактури, щоб видалити ті, які були порожніми.

Я додав ще один стовпець «Клієнт», куди я скопіював стовпець на знімку екрана вище та позбувся цифр перед назвою.

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

Потім наступним кроком є ​​«Вставлена ​​сума», де я взяв усі значення з поточних, 1–30, 31–60, 61–90 і від 91 до 120. Я додав усі ці стовпці разом, щоб отримати спеціальний стовпець під назвою «Додавання » .

Інший спосіб зробити це — виділити всі стовпці, перейти на вкладку «Додати стовпець» , клацнути «Стандартний», а потім клацнути «Додати» .

Це дасть вам поле Addition без необхідності вводити щось у power query.

Потім я видалив усі вихідні стовпці: Current, 1 – 30, 31 – 60, 61 – 90 і 91 – 120. Після цього я просто перейменував його на Balance .

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

Дата автоматично з’явилася як текстова функція, тому на цьому кроці я змінив її на функцію дати.

Створення колонки віку

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

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

А потім я перейменував стовпець з Age на Days Aged . Потім я додав ще два умовні стовпці. Перший — для стовпця старіння, щоб повторно категоризувати ці елементи.

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

Висновок

Отже, ось як я знайшов своє рішення для цієї проблеми тижня. Коли я натискаю «Закрити» та «Застосовую», ось результати.

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

Джаррет