Резюме

Какво е Get & Transform?

• Get & Transform е инструмент за преобразуване на данни за използване в софтуерните пакети Microsoft Excel и Power BI.
• Данните често пристигат в неструктурирани формати, което кара ETL (извличане, преобразуване и зареждане) да обработва досаден процес на ръчни решения.
• Get & Transform автоматизира и ускорява процеса на почистване и организиране на такива сурови данни, което в крайна сметка подпомага аналитичната задача за разкриване на наблюдения и тенденции.
• Някои примери за функционалност, предоставена от Get & Transform, включват: Премахване на колони, групиране на данни, разделяне на низове на поднизове и добавяне на редове от друга таблица.
• За поддържане на работни потоци в рамките на Excel, Get & Transform е отличен инструмент, който може лесно да бъде обяснен и демонстриран на съответните заинтересовани страни.

Как мога да използвам Get & Transform?

• Достъпът в Excel е чрез Вземете и трансформирайте данни раздел в рамките на Данни раздел. В Power BI съществува в Външни данни раздел на У дома раздел.
• Зареждане на CSV файлове: Импортирането на CSV чрез Get & Transform позволява той да бъде почистен и направен „по-тесен“ или „по-широк“, за да подпомогне пивотирането на данни. Тези инструкции могат да бъдат запазени и след това повторени за бъдещ импорт.
• Работа с текстови низове: Като значително подобрение спрямо Текст в колони функционалността в Excel, Get & Transform може бързо да анализира и разделя комбинираните текстови и числови низове в отделни колони.
• Различни източници на данни: С приемането на широк спектър от входни файлове е възможно да се работи с различни източници, като същевременно се поддържа постоянно и нормализирано качество на изхода.
• Персонализиране с код: Езикът M е функционалният код, използван в Get & Transform и е възможно да се напишат персонализирани заявки за повече поръчкови заявки.

В тази епоха на езера за данни и петабайтни бази данни, шокиращо е колко често все още получавам данни под формата на CSV, текстови и Excel файлове. Докато съвременната аналитика се фокусира върху авангарден напредък в алгоритмите за машинно обучение, ежедневната трудност на Анализ на данни все още е ръчен процес за намиране, компилиране и разправяне на различни типове данни.

За финансовия анализатор данните често пристигат като електронна таблица на Excel, но също толкова често това е изхвърляне на данни в CSV или заявка в база данни на SQL. Понякога данните са подредени в объркващо оформление или не разполагат с всички необходими компоненти за анализ. Времето, прекарано в пречистване на тези данни, е ценно загубено време за анализатора, но понякога тази задача се приема като необходимо зло, което трябва да бъде толерирано.

Какво прави Get & Transform?

Решението на този често срещан проблем всъщност е доста достъпно: Excel и Power BI разполагат с цял набор от инструменти за трансформация на данни, за които малко потребители са запознати, на име Вземете и трансформирайте (по-рано известен като Power Query). Използването на неговата вградена функционалност за извличане, преобразуване и зареждане (ETL) позволява на финансовите анализатори да се свързват безпроблемно към своите източници на данни и да стигат до по-бързи данни.

Докато извличаме данни за зареждане в Excel или Power BI, обикновено трябва да извършим някои трансформации на данните. Някои примери за манипулиране на данни ще включват:

• Премахване на колони,
• Филтриране на данните,
• Групиране на данните,
• Въртене / депитиране на данните,
• Разделяне на низове на поднизове,
• Извличане на ключови думи от низове,
• Добавяне на редове от друга таблица и
• Обединяване на две таблици с измерения.

На диаграмата по-долу виждаме, че Get & Transform изпълнява тази досадна роля на предварителна обработка на данните, преди да бъдат заредени.

Защо трябва да използвате Get & Transform?

Защо си струва да се научите как да използвате Get & Transform? Е, когато разгледам за какво лично съм използвал тази функционалност, тя ми предложи податлив набор от инструменти за:

• Зареждане на цяла папка текстови файлове в една таблица с данни
• Преобразуване на експортирани счетоводни файлове в смилаемо оформление
• Зареждане на милиони редове за продажби директно в Power Pivot
• Групиране на ежедневните данни в управляеми месечни резултати, преди да ги импортирате в Excel
• Сплитиране на данни от друга таблица чрез присъединяване към съответстващи колони

Като цяло, когато получа нови данни, ще ги проуча с помощта на Get & Transform, преди да ги заредя в Power Pivot. Това ми позволява да видя какви трансформации може да са необходими и бързо да извърша някои пивоти и групировки на данните, за да формулирам рамка за анализ. В много случаи на този етап ще установя, че имам нужда от повече данни или че има проблеми с данните. Използвайки платформа, базирана на Excel, мога бързо да повторя с моя източник на данни, за да намеря тези аномалии на данните.

В крайна сметка решението да останете в Excel или да преместите анализа на данни на друга платформа ще зависи от аудиторията и повторяемостта и разпространението на анализа. Ако моите клиенти използват само Excel, тогава почти винаги ще използвам Get & Transform за зареждане на данните, Power Pivot за извършване на анализа и Excel за създаване на обобщените таблици и диаграмите. За клиента това ще се почувства безпроблемно, тъй като всичко се помещава в Excel.

Ако обаче клиентът ми:

1. Иска да използва друг инструмент за визуализация,
2. Има множество потребители, които ще опресняват данните, или
3. Необходимо е да се използват модели за машинно обучение,

След това ще използвам Get & Transform само за първоначално проучване на данни и след това ще преместя тежкото вдигане R .

Как да влезете в Get & Transform в Excel или Power BI

В предишните версии на Excel Power Query е добавка, която може да бъде инсталирана, за да помогне с функциите ETL. В Excel 2016 и Power BI обаче тези инструменти са по-тясно интегрирани. В Excel 2016 те могат да бъдат достъпни чрез Данни и след това Вземете и трансформирайте данни раздел.

В Power BI функционалността съществува на У дома в раздела Външни данни раздел.

В тази статия моите примери се намират в Power BI, но интерфейсът е почти идентичен с този на Excel. Ще посоча разликите, когато възникнат, така че урокът трябва да има смисъл и за двата типа потребители.

1. Зареждане на CSV файлове

За да подпомогна този урок, създадох няколко примера за данни за продажби за измислен търговец на дребно, който продава екипировка и облекло на открито. Във всеки от тези примери данните ще бъдат получени по различни начини, за да се демонстрират реалистични методи за изхвърляне на данни.

Като първоначален пример ще видим данните, представени като голямо изхвърляне на данни в CSV файл. Усложняващият фактор е, че данните се представят с множество колони, представящи различни магазини. В идеалния случай бихме искали да импортираме и трансформираме данните в по-използваемо оформление.

По-долу е екранна снимка на това как изглежда суровият CSV:

Защо бихме искали да променим това? За да се възползвате от възможностите за взаимоотношения, които са възможни в тези приложения. Ще видим тази игра по-нататък в дискусията.

Засега нека приемем, че трябва да разглеждаме данните като „по-тясна и по-висока“ структура, а не като „по-широка и по-кратка“. Първата стъпка е да заредите CSV; след това ще започнем да „депитираме“ данните.

Както можете да видите, крайната структура на данните е по-тясна от първоначалните данни и много по-дълга. Друг момент е, че докато кликваме върху различни действия, инструментът отдясно генерира списък от приложени стъпки, използвани за изграждане на заявката. Важно е да разберете, че това се случва на заден план, тъй като ще бъде преразгледано по-късно.

Get & Transform изглежда и се държи по подобен начин между Power BI и Excel в по-голямата си част. В Excel обаче след щракване Затворете и заредете , има един допълнителен ред. На фигурата по-долу можем да превключваме между това дали искаме да заредим данните в:

1. Таблица в Excel,
2. Обобщена таблица, създадена спрямо данните,
3. Сводна диаграма, създадена спрямо данните, или
4. „Само създаване на връзка.“

В допълнение, ние също имаме възможност да или не Добавете тези данни към модела на данни . Поставянето на отметка в това квадратче зарежда данните в a Power Pivot маса. Ако ще анализираме данните в Power Pivot, съветвам да изберете Само Създаване на връзка и след това се уверете, че Добавете тези данни към модела на данни е избрана опция. Ако данните са в рамките на ограничението за редове в Excel и ние предпочитаме да извършваме нашия анализ в Excel, тогава просто изберете Таблица .

В следващия клип ще видим, че причината, поради която форматирахме данните да бъдат дълги и слаби, е така, че да можем да анализираме продажбите не само по магазин, но и по регион и държава. За да изпълним тази задача, ще импортираме таблица, която съответства на всеки магазин към регион и държава. Ще видим по-долу, че можем бързо да създаваме отчети, които показват продажбите от тези различни групировки.

Можете да си представите как този тип възможности за трансформация на данни в Excel или Power BI може да бъде приложен мощно във всеки случай, когато имаме динамични групи от данни, като например:

• Събиране на ежедневни данни в седмици, месеци и тримесечия;
• Групиране на търговския персонал в отдели и региони; или
• Съпоставяне на SKU с типовете продукти.

Въпреки че тази статия се занимава с CSV и други файлове на Excel, Get & Transform се справя с широк спектър от типове данни. След като заявката бъде създадена, тя може да се обнови с течение на времето, когато данните се променят.

2. Работа с текстови низове

За да демонстрирам способността на Get & Transform да манипулира низове, създадох друг набор от данни, който имитира текстов файл, показващ счетоводни транзакции от главната книга на фирмата (GL).

Забележете как номерът и името на акаунта се появяват в един и същ низ? В Power BI можем лесно да анализираме номера и името на акаунта в отделни полета.

В това видео можете да видите, че след като разделих колоната, инструментът предположи, че новата лява страна на полето Акаунт трябва да бъде число и създава стъпка „Променен тип1“. Тъй като в крайна сметка искаме това поле като низ, можем да продължим и да изтрием стъпката ръчно под приложените стъпки.

След това вземаме едни и същи данни и създаваме сметкоплан с картографиране на категории акаунти.

Защо да преминем през всички тези стъпки, за да картографираме няколко номера на сметки? Истинска главна книга може да бъде стотици или дори хиляди сметки. Тази заявка за бързо картографиране, както показахме, ще се мащабира до това ниво без допълнителна работа.

3. Работа с различни източници на данни

Get & Transform поддържа много различни източници на данни. Макар и да не е изчерпателен списък, по-долу са някои примери:

Текстов файл Excel Facebook Adobe Analytics Google Analytics Salesforce Лазурен Redshift Искра SQL Server SAP HANA Терадата Google BigQuery

Лично аз опитах само около половината от връзките в горния списък. Всеки от съединителите, които използвах, беше доста здрав; От сурови данни стигнах до прозрения без обременителен труд. Също толкова важно е, че той служи като валидатор между различни източници на данни, като гарантира, че крайните изходи имат нормализирано ниво на контрол на качеството.

4. Персонализиране на кода с езика M

На заден план Get & Transform генерира код всеки път, когато щракнем върху бутон в инструмента или направим избор. По-долу е даден пример за това как бихте получили достъп до кода за създадената от нас заявка за картографиране на акаунти:

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

Границите на Get & Transform

Excel има тенденция да достига своите граници, когато се опитате да експортирате повече от един милион реда. В случаите, когато съм преобразувал милиони редове с Get & Transform, единственият начин да изпратя негрупирани редове е чрез досадни хакове или заобикаляне. Също така установих, че заявките за получаване и преобразуване могат да бъдат нестабилни за разполагане на множество потребители, особено ако използвате множество източници на данни и се присъединява. В тези случаи винаги ще използвам R, за да разположа дублиращите се пребори с данни. И накрая, Excel не е създаден за по-усъвършенствано моделиране на данни. Можете да извършвате линейни регресии доста бързо, но освен това ще трябва да използвате по-строга платформа.

След като казах всичко това, откривам, че Excel е това, което повечето от клиентите ми са най-удобни. Excel все още е най-важният инструмент в арсенала на финансов анализатор. Чрез включването на функцията Get & Transform, Excel и Power BI стават още по-мощни чрез редица източници на данни, които те могат да приемат.

Разбиране на основите

Каква е концепцията за ETL?

Извличането, трансформирането и зареждането е процесът на преместване на данни от различни източници в централизирано хранилище за данни.

За какво се използва Power BI?

Power BI е софтуер за бизнес анализи от Microsoft. Той предоставя дълбоки възможности за визуализиране на данни и създаване на автоматизирани отчети и табла за управление.

За какво се използва езерото за данни?

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