В тази епоха на езера за данни и петабайтни бази данни, шокиращо е колко често все още получавам данни под формата на CSV, текстови и Excel файлове. Докато съвременната аналитика се фокусира върху авангарден напредък в алгоритмите за машинно обучение, ежедневната трудност на Анализ на данни все още е ръчен процес за намиране, компилиране и разправяне на различни типове данни.
За финансовия анализатор данните често пристигат като електронна таблица на Excel, но също толкова често това е изхвърляне на данни в CSV или заявка в база данни на SQL. Понякога данните са подредени в объркващо оформление или не разполагат с всички необходими компоненти за анализ. Времето, прекарано в пречистване на тези данни, е ценно загубено време за анализатора, но понякога тази задача се приема като необходимо зло, което трябва да бъде толерирано.
Решението на този често срещан проблем всъщност е доста достъпно: Excel и Power BI разполагат с цял набор от инструменти за трансформация на данни, за които малко потребители са запознати, на име Вземете и трансформирайте (по-рано известен като Power Query). Използването на неговата вградена функционалност за извличане, преобразуване и зареждане (ETL) позволява на финансовите анализатори да се свързват безпроблемно към своите източници на данни и да стигат до по-бързи данни.
Докато извличаме данни за зареждане в Excel или Power BI, обикновено трябва да извършим някои трансформации на данните. Някои примери за манипулиране на данни ще включват:
На диаграмата по-долу виждаме, че Get & Transform изпълнява тази досадна роля на предварителна обработка на данните, преди да бъдат заредени.
Защо си струва да се научите как да използвате Get & Transform? Е, когато разгледам за какво лично съм използвал тази функционалност, тя ми предложи податлив набор от инструменти за:
Като цяло, когато получа нови данни, ще ги проуча с помощта на Get & Transform, преди да ги заредя в Power Pivot. Това ми позволява да видя какви трансформации може да са необходими и бързо да извърша някои пивоти и групировки на данните, за да формулирам рамка за анализ. В много случаи на този етап ще установя, че имам нужда от повече данни или че има проблеми с данните. Използвайки платформа, базирана на Excel, мога бързо да повторя с моя източник на данни, за да намеря тези аномалии на данните.
В крайна сметка решението да останете в Excel или да преместите анализа на данни на друга платформа ще зависи от аудиторията и повторяемостта и разпространението на анализа. Ако моите клиенти използват само Excel, тогава почти винаги ще използвам Get & Transform за зареждане на данните, Power Pivot за извършване на анализа и Excel за създаване на обобщените таблици и диаграмите. За клиента това ще се почувства безпроблемно, тъй като всичко се помещава в Excel.
Ако обаче клиентът ми:
След това ще използвам Get & Transform само за първоначално проучване на данни и след това ще преместя тежкото вдигане R .
В предишните версии на Excel Power Query е добавка, която може да бъде инсталирана, за да помогне с функциите ETL. В Excel 2016 и Power BI обаче тези инструменти са по-тясно интегрирани. В Excel 2016 те могат да бъдат достъпни чрез Данни и след това Вземете и трансформирайте данни раздел.
В Power BI функционалността съществува на У дома в раздела Външни данни раздел.
В тази статия моите примери се намират в Power BI, но интерфейсът е почти идентичен с този на Excel. Ще посоча разликите, когато възникнат, така че урокът трябва да има смисъл и за двата типа потребители.
За да подпомогна този урок, създадох няколко примера за данни за продажби за измислен търговец на дребно, който продава екипировка и облекло на открито. Във всеки от тези примери данните ще бъдат получени по различни начини, за да се демонстрират реалистични методи за изхвърляне на данни.
Като първоначален пример ще видим данните, представени като голямо изхвърляне на данни в CSV файл. Усложняващият фактор е, че данните се представят с множество колони, представящи различни магазини. В идеалния случай бихме искали да импортираме и трансформираме данните в по-използваемо оформление.
По-долу е екранна снимка на това как изглежда суровият CSV:
Защо бихме искали да променим това? За да се възползвате от възможностите за взаимоотношения, които са възможни в тези приложения. Ще видим тази игра по-нататък в дискусията.
Засега нека приемем, че трябва да разглеждаме данните като „по-тясна и по-висока“ структура, а не като „по-широка и по-кратка“. Първата стъпка е да заредите CSV; след това ще започнем да „депитираме“ данните.
Както можете да видите, крайната структура на данните е по-тясна от първоначалните данни и много по-дълга. Друг момент е, че докато кликваме върху различни действия, инструментът отдясно генерира списък от приложени стъпки, използвани за изграждане на заявката. Важно е да разберете, че това се случва на заден план, тъй като ще бъде преразгледано по-късно.
Get & Transform изглежда и се държи по подобен начин между Power BI и Excel в по-голямата си част. В Excel обаче след щракване Затворете и заредете , има един допълнителен ред. На фигурата по-долу можем да превключваме между това дали искаме да заредим данните в:
В допълнение, ние също имаме възможност да или не Добавете тези данни към модела на данни . Поставянето на отметка в това квадратче зарежда данните в a Power Pivot маса. Ако ще анализираме данните в Power Pivot, съветвам да изберете Само Създаване на връзка и след това се уверете, че Добавете тези данни към модела на данни е избрана опция. Ако данните са в рамките на ограничението за редове в Excel и ние предпочитаме да извършваме нашия анализ в Excel, тогава просто изберете Таблица .
В следващия клип ще видим, че причината, поради която форматирахме данните да бъдат дълги и слаби, е така, че да можем да анализираме продажбите не само по магазин, но и по регион и държава. За да изпълним тази задача, ще импортираме таблица, която съответства на всеки магазин към регион и държава. Ще видим по-долу, че можем бързо да създаваме отчети, които показват продажбите от тези различни групировки.
Можете да си представите как този тип възможности за трансформация на данни в Excel или Power BI може да бъде приложен мощно във всеки случай, когато имаме динамични групи от данни, като например:
Въпреки че тази статия се занимава с CSV и други файлове на Excel, Get & Transform се справя с широк спектър от типове данни. След като заявката бъде създадена, тя може да се обнови с течение на времето, когато данните се променят.
За да демонстрирам способността на Get & Transform да манипулира низове, създадох друг набор от данни, който имитира текстов файл, показващ счетоводни транзакции от главната книга на фирмата (GL).
Забележете как номерът и името на акаунта се появяват в един и същ низ? В Power BI можем лесно да анализираме номера и името на акаунта в отделни полета.
В това видео можете да видите, че след като разделих колоната, инструментът предположи, че новата лява страна на полето Акаунт трябва да бъде число и създава стъпка „Променен тип1“. Тъй като в крайна сметка искаме това поле като низ, можем да продължим и да изтрием стъпката ръчно под приложените стъпки.
След това вземаме едни и същи данни и създаваме сметкоплан с картографиране на категории акаунти.
Защо да преминем през всички тези стъпки, за да картографираме няколко номера на сметки? Истинска главна книга може да бъде стотици или дори хиляди сметки. Тази заявка за бързо картографиране, както показахме, ще се мащабира до това ниво без допълнителна работа.
Get & Transform поддържа много различни източници на данни. Макар и да не е изчерпателен списък, по-долу са някои примери:
Текстов файл Excel Facebook Adobe Analytics Google Analytics Salesforce Лазурен Redshift Искра SQL Server SAP HANA Терадата Google BigQueryЛично аз опитах само около половината от връзките в горния списък. Всеки от съединителите, които използвах, беше доста здрав; От сурови данни стигнах до прозрения без обременителен труд. Също толкова важно е, че той служи като валидатор между различни източници на данни, като гарантира, че крайните изходи имат нормализирано ниво на контрол на качеството.
На заден план Get & Transform генерира код всеки път, когато щракнем върху бутон в инструмента или направим избор. По-долу е даден пример за това как бихте получили достъп до кода за създадената от нас заявка за картографиране на акаунти:
Кодът използва функционален език с име М , който автоматично се генерира за основни случаи на употреба. Въпреки това, за по-сложни препратки на данни, можем да редактираме и напишем свой собствен код. В повечето случаи ще правя само незначителни промени в този код. При по-сложни трансформации мога да напиша по-голямата част от кода от нулата до етап временни маси , или за по-сложно изпълнение присъединява .
Excel има тенденция да достига своите граници, когато се опитате да експортирате повече от един милион реда. В случаите, когато съм преобразувал милиони редове с Get & Transform, единственият начин да изпратя негрупирани редове е чрез досадни хакове или заобикаляне. Също така установих, че заявките за получаване и преобразуване могат да бъдат нестабилни за разполагане на множество потребители, особено ако използвате множество източници на данни и се присъединява. В тези случаи винаги ще използвам R, за да разположа дублиращите се пребори с данни. И накрая, Excel не е създаден за по-усъвършенствано моделиране на данни. Можете да извършвате линейни регресии доста бързо, но освен това ще трябва да използвате по-строга платформа.
След като казах всичко това, откривам, че Excel е това, което повечето от клиентите ми са най-удобни. Excel все още е най-важният инструмент в арсенала на финансов анализатор. Чрез включването на функцията Get & Transform, Excel и Power BI стават още по-мощни чрез редица източници на данни, които те могат да приемат.
Извличането, трансформирането и зареждането е процесът на преместване на данни от различни източници в централизирано хранилище за данни.
Power BI е софтуер за бизнес анализи от Microsoft. Той предоставя дълбоки възможности за визуализиране на данни и създаване на автоматизирани отчети и табла за управление.
Езерото с данни е едно хранилище за всички източници на данни в организацията. Това може да включва структурирани и неструктурирани джобове с данни, които в крайна сметка могат да бъдат използвани и обработвани, когато е необходимо.