#66 (3/3) Групиране (Power Query)

    Остана да видим и последното решение на задачката с групирането на текстови данни. За целта ще използваме Power Query. Инструментът е част от Power BI решенията на Microsoft и освен в Excel го има в Pоwer BI Desktop и други Power BI решения, които имат за задача да импортират и подготвят данни.

Стъпка 1: Заставаме в областта, съдържаща входните данни и изпълняваме командата Data/From Table/Range

Стартиране на Power Query с входни данни от текущата раб. книга

    Стъпка 2: Данните се зареждат в Power Query, но тук е един от случаите в които PQ проявява своята "интелигентност" в повече. След зареждането прави допълнителна стъпка за определяне на типа на данните и решава, че първата колонка трябва да се трансформира в тип Time (време). Вие ще кажете, че това е правилно решение и данните са наистина време, но за по-нататъшната обработка е необходимо данните да си останат като текст. Моят съвет е да следите стъпките и да очаквате неочакаването:) В случая просто трябва да се премахне стъпката Changed Type (чрез X-a пред стъпката).

Добавена стъпка за промяна на типа на колонката

След премахване на стъпката

    Стъпка 3: Изпълнява се команда Home/Group By за колона за групиране се избира колона "Направление", а за колона с данни се избира "Час на тръгване". Обърнете внимание, че за операция е избрана функция Sum!! За съжаление, създателите на инструмента са решили да покажат само основните функции за работа с числа и се налага да се правят "ръчно-(крачни;)" операции!

Команда GroupBy

    Стъпка 4: Както вече писах, в момента формулата е абсолютно грешна и резонно в новата колонка има съобщение за грешки!

Грешка при изпълнение

На ръка трябва малко да "пипнем" формулата в реда за редактиране:) И тя трябва да стане:

= Table.Group(Source, {"Направление"}, {{"Часове на тръгване", each Text.Combine([Час на тръгване],", "), type text}})

    Обърнете внимание, че освен подмяната на функцията  List.Sum с Text.Combine сме добавили и втори параметър ", " към нея!!

    Забележка: За сега нямам намерение да ви правя дисекции на формули писани на езика "М" (езикът използван в Power Query), защото излиза леко извън рамките на блога. Но понеже надали ще направя отделни блогове за Power BI сигурно в един момент ще започна да ви разказвам и за "М" и за "DAX"! :) В интерес на истината можехме просто да направим нова стъпка и да напишем изцяло формулата, но както писах, голяма част от Power BI юнаците са на принципа "щракни и гледай какво става" без да вникват в същността на нещата и че в крайна сметка всички кликове се обръщат в заявки на "М"!

Коригирана формула

    Стъпка 5: Зареждането на преобразуваните данни обратно в Excel се извърша чрез командата File/Close & Load (има и бутон на лентата Home). Обърнете внимание, че когато сте в новосъздадената таблица, освен лентата за работа с таблици, се появява и лентата Query, в която се намират инструментите за редактиране и работа със запитването.

Импортиране на данните

    

    Стъпка 6: Настройки на запитването. В настоящия момент запитването е статично. Т. е. при промяна на входните данни резултатната таблица няма да се опресни! За целта има няколко подхода:

    - да научите потребителите да натискат бутона Refresh от лентата Query. За целта трябва да се намират върху резултатната таблица!

    - Да се направя настройки, улесняващи новаци. За целта се натиска бутона Properties от лентата Query.

    Refresh every ... - Опресняване на всеки хх минути. Т. е. осигуряваме актуални данни след изтичането на определен период. Т. е. казваме на нашия човек "пий едно кафе и виж":) Тук риска е да се губи излишно време, ако няма честа актуализация на входните данни.

    Refresh data when opening file - Да опреснява данните всеки път, когато се отваря файла. Препоръчвам тази опция да е включена.

    Refresh this connection on Refresh All - Да се опреснява запитването при натискането на бутон Refresh All от лента Data. Също е добра идея да включите тази опция, за да има алтернатива за опресняване особено, ако има не само едно запитване!

Ми това е:)

П.П. Работната книга с решенията се намира на  адрес работна книга GroupBy.

П.П.2 Показах ви три варианта за решаване на един проблем. Вие си преценявате кой от подходите ви е най на сърце:) За тренировка опитайте да направите обратната на тази таблица. Т. е. да групирате направленията по час на тръгване. Успех!

#66 (2/3) Групиране (нинджа с Office 365)

    Тук не е за хора със слаби нерви и за хора, които са на ниво IF() и Vlookup():) Та бременни и деца под 18 да не четат:) Шегувам се;) Точно младежите са тези, които може до пенсионирането си да изучат всички неща в Excel:) Но се съмнявам в това, ако Microsoft продължат да бълват новости със същата скорост.

    Та както се вижда от заглавието за да реализирате това решение трябва да разполагате с нов меч. Трябва да имате инсталиран Office 365, който да е обновен поне към края на 2022 година!

Стъпка 1: В клетка A1 въвеждате формулата : 

=REDUCE({"Направление"\"Часове на тръгване"}; SORT(UNIQUE(Data[Направление])); LAMBDA(acc; town; VSTACK(acc; HSTACK(town; TEXTJOIN(", ";;SORT(UNIQUE(CHOOSECOLS(FILTER(Data; Data[Направление]=town);1))))))))

и ....

това е! 😮 

    Всичко е готово барабар със заглавията на колоните и динамично ще се опреснява, ако променяте таблицата с първични данни! Няма нужда от копиране, акробатики с Ctrl+Shift+Enter и т.н.! Найс а? 💪

Резултат от формулата

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

    Ще се опитам да "разглобя" тази дълга формула и обясня отделните и парчета и тяхното взаимодействие.

    Забележка: Прочетете внимателно статията за разделителите #41 Разделители и масиви за да няма изненади, породени от различните настройки на Windows. Най-добре изтеглете примерната работна книга за да видите как изглеждат формулите при вас (при зареждане на раб. книга се отчитат локалните настройки и формулите  се настройват спрямо тези настройки!).

    Дисекция 0: Данните се намират в таблица с име Data. Таблицата има две колонки: "Час на тръгване" и "Направление". Важно е да се отбележи реда на колонките, който е важен за правилната работа на формулата!! 

    Дисекция 1:  =REDUCE({"Направление"\"Часoве на тръгване"}; SORT( UNIQUE(Data[Направление])) ; LAMBDA(.......) )

    Функцията Reduce() служи за натрупване на резултатът от обработката на елементи на масив в една променлива (нарича се "акумулатор"). За всеки елемент от масива се изпълнява определена функция. Функцията Redice има три параметъра:

    - начална стойност на акумулатора. В нашия случай това е заглавния ред на резултата - масив от две КОЛОНИ (виж отново #41!!): {"Направление"\"Часoве на тръгване"}

-     масив от стойности, които се обработват. В нашия случай подаваме сортиран списък, съдържащ уникалните направления. SORT( UNIQUE(Data[Направление])).

Резултат на Sort(Unique())

- действие, което обработва елементите от масива. Декларирането на действието става чрез функцията Lamda().

Дисекция 2: LAMBDA(acc; town; VSTACK())

    Функцията Lambda служи за деклариране на функция! :):):) Спокойно! Ще имаме отделна тема за това. В общи линии, Lambda описва входните параметри и самото действие за обработването на входа. Reduce изисква да се опише функция с ДВА входни параметъра:
    - входен параметър, съдържащ текущата стойност на акумулатора  (в нашия случай съм го кръстил "acc")
    - входен параметър, задържащ стойността на текущия елемент на входния масив, който обработваме. В нашия случай съм кръстил този параметър "town" (може би по-правилното име е "destination", но името няма значение!).
    Последния параметър на Lambda е самото действие, което ще се изпълнява за всеки елемент  на входния масив (всяко направление).

Дисекция 3: VSTACK(acc; HSTACK(town; TEXTJOIN()))

Обработка на елементите на масива

    За всеки град се изпълнява VStack. Функцията служи за добавяне на елемент(и) ПОД елементите на даден масив. Т.е. се извършва В(ето от къде е V-то)ертикално слепване на масиви. В нашия случай към текущото състояние (стойността на акумулатора acc) се добавя един ред за направлението. Редът се състои от две клетки: направлението (town) и часовете за тръгване (получават се чрез функцията TextJoin). Функцията HStack слепва масиви Х(H)оризонтално!  Т.е. за всяко направление правим масив от две клетки (направление и часове) и получения масив(ред) го долепяме под елементите на текущото решение!

    Дисекция 4: TEXTJOIN(",  ";; SORT(UNIQUE(CHOOSECOLS( FILTER(Data; Data[Направление]=town); 1))))

Тази част от функцията служи за получаване на клетката с часовете на тръгване.

- FILTER(Data; Data[Направление]=town)  - филтрира само редовете, които са за текущото направление. Резултатът е масив от две колонки!

- CHOOSECOLS(FILTER();1) - връща само първата колонка (колонката с часовете на тръгване). За това е важно да се знае подредбата на входните данни!.

- SORT(UNIQUE(CHOOSECOLS()))- премахва дублажите и сортира. Ако сте сигурни, че във входните данни няма дублирани часове в едно направление, може да махнете Unique!

- TEXTJOIN(",  ";; SORT()) - Слепва (като текст) данните от масива като за разделител използва запетая (първия параметър на функцията). Втория параметър на функцията е начина за работа с празните елементи на масива и използваме стойността по подразбиране. За това има два разделителя (;;)! (Честно казано, това не е добър стил и по-добре явно да задавате стойност на параметрите!).

Ми това е дисекцията на формулата. Кой разбрал, разбрал:) Който иска да пита да пише коментар или да ми пише мейл:)

    Ъпгрейд: По принцип е тъпа (дали?) идея да генерирате и заглавието на самата таблица. За това може да изтриете (дроп-нете) първия ред от резултата! Но тогава си губи и смисъл да задавате и конкретна начална стойност на акумулатора! Ето и "подобрения" вариант на функцията и нейния резултат:

=DROP(REDUCE("";SORT(UNIQUE(Data[Направление]));LAMBDA(acc; town; VSTACK(acc; HSTACK(town; TEXTJOIN(", ";; SORT(UNIQUE(CHOOSECOLS(FILTER(Data; Data[Направление]=town);1))))))));1)

Резултат от "подобрения" вариант

П.П. Работната книга с решенията се намира на  адрес работна книга GroupBy.

#66 (1/3) Групиране (по динозавърски)

 Да понагазим малко в сериозните неща. Примерът, който ще разгледам е в три серии и ще илюстрира три подхода на работа в Excel, които използвам:

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

- "нинджа" - тук, най-често, се използват дълги функции, които изискват доста добро познаване на тънкостите на Excel. Често тези  трикове са свързани с определена (нова) версия на Excel. Тях ги препоръчвам за напреднали и хора, които искат с примери да разберат новите функции. Надали ще сваляте мадами с тях, но можете да блеснете на някое интервю за работа (или да се окаже, че знаете повече от този, който ви прави интервю, което си е драма;)

- "BI Guy";) - умишлено съкратих Power BI Guy за да стане по-весело и двусмислено;) Та в случая Power BI идва от Business Intelligence и е цял пакет от инструменти и онлайн услуги на Microsoft за лесен бизнес анализ на данни. Тези инструменти са цели паралелни вселени и изискват допълнително усилия за изучаване. Доста съм раздвоен по въпроса за тяхното използване в моя блог. От една страна те са лесни и често са от тип "point and click" (посочи и щракни). Но от друга страна, тези инструменти имат собствени езици (DAX, M) за описание, импортиране и анализ на данните, които са много по различни от Excel. И не знам до колко мястото на тяхното използване е в блог за Excel. За това ще се огранича само със "click" функционалностите им, без да влизам в дълбочина. Освен, ако няма голям интерес от вас към тези инструменти:)

Та преди да ми кажете "ей, това мога с един клик да го направя", изчакайте и трите серии на темата:)

Та към проблемът, който ще решаваме.

 Имаме списък с часове за тръгване на автобуси и искаме да ги групираме по направления
за по-лесен преглед.

Първоначални данни и групирани данни по направления

    Стъпка 0: Тук ще ми трябват допълнителни колонки и за това ще си направя нов работен лист (Dino) и ще копирам данните в него. 

    Стъпка 1: За групирането ще ми трябва да сортирам данните по направление и по час на тръгване. Доста ми е забавно, когато хора, които уж разбират Excel сортират само чрез падащия списък до имената на колонките и се чудят как да сортират по две или повече колонки. За целта се използва командата Sort от лентата Data. Там се задават нивата (в случая две) на сортиране.

Сортировка и сортирани данни

    Стъпка 2: Ще добавя нова колонка "Часове на тръгване" в която ще "натрупвам" часовете за всяко направление. В тази колонка има следната формула:

=IF([@Направление]<>B1;[@[Час на тръгване]];C1 & ", " & [@[Час на тръгване]])

    Забележка: Това е таблична формула и ако не сте ОК със странните записвания може да замените [@Направление] с B2, а [@[Час на тръгване]] с A2!

    В тази формула няма (според мен де) голяма магия. Проверяваме дали текущото направление се различава от предходния ред. Ако е така, означава, че това е първия час на тръгване за съответното направление и се взема директно стойността на колонка А! Ако не е първия час, се взема предишната стойност от колонка C и към нея се добавя запетая и часът на тръгване.

Формула за натрупване на часовете за тръгване по направления

    Стъпка 3: Ще добавя нова колонка, която ще индикира дали това е последния ред от даденото направление. Тук формулата е: 

=IF([@Направление]<>B3;"Да";"Не")

    Тук, за разлика от предходната формула, се сравнява направлението с направлението от СЛЕДВАЩИЯ ред.

Формула за намиране на последния ред за дадено направление

    Стъпка 4: Филтрираме само редовете, които се явяват последни редове за даденото направление. За филтриране използваме филтъра до името на колоната.

Филтриране на данните

    Стъпка 5: Скриваме колонки A и C (десен бутон Hide върху името на колонката).

Скриване на колонка

Ми готово! 😇 Така го правят динозаврите 💚

    П.П. Имайте предвид, че някои от стъпките могат да се направят автоматизирано и като цяло, този вариант на решение изисква ръчна работа при добавяне на нови часове в основната таблица. Но както вече писах, операциите тук са прости, бързо се изпълняват, а формулките са лесни и не изискват много мисловна дейност;) И цялото решение се прави бързо. Но то не е подходящо, ако искате да дадете готов инструмент в ръцете на някой, който хал хабере си няма от Excel. За това четете следващите серии на темата:)

    П.П.2. Работната книга с решенията се намира на  адрес работна книга GroupBy.