В първата статия, посветена на Power BI за Ексел, стана ясно, че той се състои от три елемента – Power Query, Power Pivot, Power View. Сега ще покажа нагледен пример как работи и каква работа върши първият от тях – Power Query. По мое мнение овладяването му е много бързо и лесно, а добавената стойност за потребителите на Ексел – изключително голяма.
Много интересни възможности има при сваляне на данни от Интернет. И преди в Ексел можеше да се зареждат данни от Интернет с т.нар. Web Query. Но Power Query може значително повече. Продължавайте да четете и ще видите как може да свалите текст на песен в Ексел.
Избрал съм си песента „Казано честно“ на Васил Найденов на следния линк:
https://textove.com/vasil-naydenov-kazano-chestno-tekst
Ето и процесът стъпка по стъпка. В случая използвам Ексел 2013, ако имате версия 2010 или 2016 може да има разлики в някои от менютата, но основните неща са същите:
- Отваряме нов файл и стартираме ново Query. В Ексел 2013 това става от таб Power Query, а след това избираме From Web
2. В появилия се прозорец пействаме линка и даваме ОК
Това съобщение да не ви притеснява. Натиснете Connect
3. Минава известно време докато Ексел обработва нещо и показва този екран:
Тук избираме Document и натискаме бутона Edit
След още малко време, вече сме в Power Query. За тези от вас, които все още не са работили с него – ЧЕСТИТО! Току-що направихте революционна крачка към времето, когато ще работите по-малко, а ще постигате повече.
Да отделим малко време и да разгледаме набързо различните менюта от картинката.
Най-отгоре е така да се кажа Ribon Tab-ът на Power Query.
По подразбиране се отваря на секцията Home. В тази секция има най-различни бутони за работа със самото Query. Указваме къде и как да се зареждат данните, можем да променим източника на данни, да обединим няколко заявки в една и т.н. В нея се намират и някои инструменти за работа с колоните и редовете, които обработваме. От тук махаме колони и редове, сортираме данните, групираме, заместваме (Replace). Бързо и лесно променяме и формата на данните – от Data Type буквално с един клик променяме текст в дата или обратно например.
Същите тези инструменти, заедно с още много са в секцията Transform.
В Add Column най-важна е частта General. От тук добавяме нови колони с различни изчисления. След малко ще ви покажа няколко примера.
Във View можем да променяме как изглежда Power Query. Например да покажем или скрием реда за формули (ограденото със синьо)
Дясната част е много интересна и важна.
Както виждате, там има две полета. Едното е Name за името на заявката. В момента то е Document и е служебно присвоено от Ексел. Можем да го променяме по всяко време. За целта маркираме думата „Document“ и пишем нещо по-говорящо за нас. Например Text ot Internet.
Съвет: Винаги пишете имената на заявките на латиница. За по-сигурно. Патил съм си аз от имена на кирилица и повече тази грешка няма да я повторя.
Секцията APPLIED STEPS буквално се превежда като Извършени Действия. Тук се записват една по една всички операции, които сме направили във заявката. Това ни дава възможност да преглеждаме какво сме направили, да правим промени, да вмъкваме нови стъпки, да трием такива, които не ни харесват.
Внимание! В Power Query няма Undo. Ако някоя операция не работи така, както сме очаквали – можем да я изтрием. Но изтрием ли я веднъж – не можем да я възстановим.
В централната част е таблицата, в която са данните. На пръв поглед това може да е леко озадачаващо – сваляме текст на песен от интернет, а виждаме таблица. Но зад всеки сайт стои база данни, а всяка една база данни е съвкупност от таблици.
За момента нашата таблица е само четири колони и един ред, но много скоро това ще се промени. Трябва само да натиснем бутона в горния десен ъгъл на колоната Table.
Появява се меню, в което оставяме отметки само на Children и Text.
Както обещах – таблицата ни стана по-голяма. Отново натискаме бутончето за разтваряне в колоната с название Children и като се отвори прозореца – оставяме отметки само на Children и Text.
Този процес може да продължава много дълго. Случвало ми се е да го повтарям повече от 30 пъти. Всеки път излиза нова колона Children с бутон за разтваряне и до нея – нова колона Text. В началото, ако филтрирате колоната Text, ще видите, че тя не съдържа нищо.
Но след определен брой повторения, които тук ще спестя, ще започне да се появява и текст, който присъства някъде на страницата.
Продължаваме да „разтваряме“ колоните Children, като оставяме отметки само на Children и Text. Накрая в колоната Text.10 виждаме каквото ни трябва – текстът на песента:
Но виждате, че има и текст, който не ни трябва (ограденото в синьо). Без притеснения – с помощта на различни инструменти на Power Query ще отстраним плявата. Но първо ви предлагам да запазим направеното. Защото все още не сме го направили, а не знаем кога нещо ще се случи и ще похабим толкова труд.
Натискаме бутона Close&Load, който се намира най-в ляво и най-отгоре. След това е добре да запазим файла.
Появяват се два нови обекта – единият е таблицата с данните, а другият панелът Workbook Queries
Нека преди да се върнем в Power Query да разгледаме таблицата. За нас важните колони са две – Text.10 и Text.3. В първата се съдържа текстът на песента, а втората – името на изпълнителя и заглавието на песента. Добре е да анализираме кое къде се намира, за да можем да изготвим план как да премахнем текста, който не ни трябва. Това е строго специфично за всяка една страница.
В нашия случай например колоната Text.3 съдържа на 7 ред този текст: „Васил Найденов – Казано честно текст | Текстове на песни Textove.com“ от тук можем да извлечем името на изпълнителя и заглавието на песента. Как – ще покажа след малко. Защо? Защото е добре да имаме тази информация подредена в табличен вид.
Да се върнем в заявката, за да продължим с работата по нея. Това става като кликнем с десния бутон върху името на заявката.
Най-напред да махнем колоните, които не ни трябват. Отиваме на Home и натискаме бутона Chose Columns, след това оставяме отметки само на Text.10 и Text.3. Вече имаме само две колони.
Най-напред ще направим колоните за Име на изпълнител и Заглавие на песен.
Първо, добавяме една нова колона от Add Column > Conditional Column. В появилото се меню последователно указваме: Ако в някой ред на колона Text.3 се съдържа текстът „Текстове на песни Textove.com“, то стойността на новата колона да бъде равна на тази в колона Text.3 в противен случай (Otherwise) стойността да е null.
Тук е важно как ще въведем стойността в полето Output. Трябва да изберем опцията Select Column от стрелкичката на бутона отляво и след това избираме колоната Text.3
Когато дадем ОК, резултатът обаче е малко по-различен от този, който очакваме – виждаме много грешки (Error). Това не е проблем. Маркираме колоната, десен бутон, избираме Replace Errors и в менюто, което ще излезе просто пишем null.
Сега вече всичко е както ни трябва – имаме колона, в която на един ред се съдържа текстът с певеца и заглавието, а всички останали са null. Време е да използваме една голяма благина в Power Query – Fill down/Fill Up. Става или с десен бутон върху колоната (ограденото в червено) или от менюто Transform (ограденото в синьо).
Първо правим Fill down, после Fill Up и крайният резултат е една колона, в която имаме само това: Васил Найденов – Казано честно текст | Текстове на песни Textove.com.
Нека да подредим малко тази колона:
Десен бутон > Split Column > By delimiter. Това може би ви напомня на Text-to-Columns. Точно така – ще разделим колоната на две, но в Power Query е много по-гъвкаво и с повече възможности.
В следващия прозорец избираме типа на делителя “Custom” и казваме да бъде: “ – “ (интервал тире интервал). В Power Query разделителят може да бъде дори цяла фраза!
Накрая посочваме, че искаме колоната да се раздели на първото място, където е разделителят, започвайки от ляво (At the left-most delimiter). Както виждате имаме и още две опции (в Text-to-Columns имаме само една).
Сега вече имаме колона с името на певеца, но в тази със заглавието стои и този текст „ | Текстове на песни Textove.com“, който не ни трябва. Тогава да го махнем – десен бутон > Replace Values и след това даваме да замени този текст с нищо.
Готово. Остана само да променим имената на колоните. Става с двоен клик върху името, то се маркира и пишем нещо по-подходящо. Другият вариант е десен бутон и избираме Rename или единичен клик и F2. Въпрос на вкус. А ето и преименованите колони:
Вероятно в този момент вече се чувствате загубени в превода. Всъщност, ако трябва да сме честни става дума за двайсетина кликвания с мишката. Не повече от 2 минути работа, когато му свикнете.
Да се върнем към таблицата. „Оригиналната“ колона Text.3 вече не ни трябва, затова – десен бутон върху нея и избираме Remove.
Остана ни още малко работа по извличане текста на песента, но ако желаете, може да си вземете кратка почивка
Починахте ли си?
Да продължаваме тогава
Текстът на песента се намира в колона Text.10. В нея има много празни редове или такива със стойност null. Можем да ги махнем по същия начин, по който филтрираме в Ексел – от стрелката за филтър в десния ъгъл на заглавието на колоната.
Сега забелязваме, че първите 16 реда са думи, които се срещат някъде на страницата но не ни трябват – „Вход“, „Регистрация“, „Забравана парола“ и т.н.
Бихме могли да ги махнем пак чрез филтъра, но по-хитрият начин е като отидем на Home > Remove rows > Remove Top rows. В следващия прозорец просто въвеждаме 16, защото искаме да махнем първите 16 реда и получаваме това:
Последните 10 реда също съдържат текст, който не ни трябва.
Премахваме ги по аналогичен начин от Home > Remove rows > Remove Bottom rows, и посочваме 10.
Резултатът е, че вече имаме таблица, която съдържа само текста на песента
Сега нека да заменим Text.10 с нещо по-подходящо като например „Текст“. След това можем да разместим колоните. Единият вариант е с влачене с натиснат ляв бутон на мишката. Той е удобен в случая, защото имаме само 3 колони. Но ако таблицата е с повече колони, по-удобно е с десен клик на мишката върху заглавието на колоната и след това да изберем Move > To End.
Готово, сега можем да натиснем бутона Close&Load и таблицата с текста на песента ще се зареди в Ексел.
Ето как изглежда таблицата в Ексел:
Нека да обобщя какво направихме:
- Създадохме Query, свързано към линка на песента
- Последователно „разтваряхме“ колоните, в които се съдържа думата Table като филтрирахме да ни излизат само Children и Text.
- Много е важно да проверяваме всеки път колоната Text като натискаме филтър бутона дали не се е появил текстът, който ни трябва. В случая той се появи на петия път, но може да бъде и много повече.
- С различни инструменти на Power Query:
- махнахме излишните колони, редове и символи
- разделихме колоната с изпълнителя и заглавието на две
- подредихме таблицата
- Накрая заредихме таблицата в Ексел
Описаните стъпки са специфични за задачата, но с аналогични действия можете да свалите практически всякаква информация от дадена страница – цени от електронен магазин, описание на стока, обява за продажба на имот или за работа, финансови данни…. Възможностите са много големи.
Логично е да се запитате – „Добре де, защо ни беше цялата тази галимация, след като можем просто да си копираме текста от сайта и да си го пейстнем в Ексел?“.
Да, ако е еднократно действие – съгласен съм. Но как ще ви се стори, ако трябва да свалите текстовете на всички песни на Васко Кеца?
В следващата статия ще ви покажа точно това – как да направим заявка, с която ще заредим текстовете на всички песни на Васил Найденов наведнъж.
Статията е част от поредица за сваляне и анализ на данни от интернет, чрез Power Query. Ето и връзките към другите статии:
- Сваляне на данни от Интернет с Power Query
- Сваляне на данни от много линкове в Интернет
- Създаване на списък с линкове
- Анализ на текст чрез Ексел
- Word clouds в Excel