Понякога сме изправени пред ситуация, в която трябва да се направи сравнение на файлове в две различни папки.
Например:
HR специалист е изпратил над 30 файла на различни мениджъри в компанията, в които те да посочат необходимите обучения за техните подчинени.
Или Финансовият отдел е изпратил файлове за годишния бюджет, които да бъдат попълнети от съответните бизнес звена.
Или Търговски мениджър е изпратил информация на десетки търговски представители да попълнят някаква информация за техните клиенти.
Без значение какъв точно е случаят в някакъв момент файловете започват да пристигат и този, който ги е изпратил трябва да направи нещо просто – да види от кой не е получил файл, за да му напомни да го изпрати.
В тази статия ще ви покажа как с помощта на Power Query правя сравнение на файлове в различни папки.
Организация на файловете
Най-напред изпратените файлове записвам в папка „Изпратени“ като те имат еднотипни имена. Например: „Budget 2022 HR v1”, „Budget 2022 Marketing v1”, „Budget 2022 IT v1” и т.н.
За целите на примера файловете са организирани по този начин:
Получените файлове записвам в друга папка „Получени“:
Както виждате някои хора са запазили името на файла, други са добавили към него дата или нещо друго. Има даже и един зевзек (кой друг, ако не Владо Кашона), който за по-лесно е изтрил думата file в името. Дали обаче съм получил от всички?
Един начин да разбера е да гледам файл по файл и да отмятам на лист или във файл от кой имам. Ако са 5-10 файла – иди дойди, но ако са 50-60?
Ето къде Power Query идва на помощ, за да създам списък на изпратените файлове, после списък на получените и накрая да ги сравня.
Списък на изпратените файлове
Най-напред в нов файл отивам на Get Data > From File > From Folder
(това във Ексел 365, за други версии е по-различно, но пак трябва да търсите From File > From Folder)
Намирам съответната папка, натискам ОК и се появява това меню.
В него се вижда как изглеждат данните и всичко, което трябва да направя е да натисна Transform Data
Когато данните се заредят в Power Query, маркирам колоната с имената на файловете и след това – десен бутон > Remove Oher Columns. Другите колони в случая не ми трябват и затова ги махам
След това: Transform > Extract > Text Between Delimiters
С това действие казвам на Power Query да остави в колоната само текста между два разделителя. Кои са тези разделители определям в следващата стъпка:
Понеже имената на файловете са еднотипни – всички започват с думата “file “ (и интервал след нея) и завършват с разширението “.xlsx”. Затова ги използвам като Start delimiter и End delimiter. Не се вижда на картинката, но след “file” съм добавил и интервал, за да не остане този излишен символ.
Ето го резултатът – подреден списък на хората, на които съм изпратил файлове
Списък на получените файлове
Следва същото за получените файлове. Няма нужда да излизам от Power Query. Просто отивам на Home > New Source > File > Folder
От там на татък изпълнявам стъпките, описани по-горе, за да стигна до тук:
Получил съм текста между “file “ и “.xlsx”, но има няколко проблема:
- Останали са добавените символи („25.01.22“, “_final”, “- Copy”) и т.н.
- На последния ред името на Владо Кашона е изчезнало. Просто защото в името на неговия файл липсва първият разделител (“file “)
Кашона е единичен случай, лесно е да го отбележа, но какво да правя с другите?
Откровено казано решението е според конкретния случай. В този забелязвам, че два пъти допълнителните символи са отделени от името с интервал, а други два – с подчертаващо тире. Затова мога първо да заменя подчертаващото тире с интервал. За целта маркирам колоната, натискам десен бутон на мишката и избирам Replace values…
След това указвам какво с какво да замени.
Сега вече мога да използвам Transform > Extract > Text Before Delimiter
Използвам за разделител интервал (съжалявам няма как да го покажа на картинката)
Натискам Advanced options, което отваря допълнително меню.
В него посочвам, че искам Power Query да търси разделителите от началото на текста към края (From the start of the input) и (много важно!) да пропусне първия разделител. Защо? Защото първият интервал е между малкото име и фамилията. Затова искам да го пропусне
И ето го резултатът:
Сравнение на файловете
Почти накрая сме. Остава само да изпълня VLOOKUP в Power Query и това ще покаже кои имена от таблицата на изпратените файлове се намират в тази на получените.
Тази операция в Power Query се изпълнява с Merge Queries от менюто Home
Тук казвам да съедини таблиците Sent (Изпратени) и received (Получени) като използваме Left Outer.
За незапознатите с теорията на базите данни Left Outer означава, че искаме новата таблица да съдържа всички редове от Sent и съдържанието на тези в received, които им съответстват. За да не го усложнявам много – това е най-често срещаният случай и затова е заложен по подразбиране.
Тук трябва да натиснем оградения бутон
А тук – ОК
И нашето сравнение на файлове е готово.
Съвсем ясно се вижда, че липсват файловете на Misho Pijev, Vania Bacova, Vlado Kashona, Yanko Koprivarov.
Е, за Владо знам, че го имам, но другите ги няма. Или по-точно – няма ги в папката. Трябва да ги потърся или поискам от съответния човек.
Няколко размишления върху казуса
Това не е ежедневна ситуация за повечето хора, но все пак се случва. С методите на обикновения Ексел тя няма как да бъде решена. VBA също би бил от помощ, но изисква писане на код, цикли, променливи. Неща които са далеч от средностатистическия потребител на Ексел. С Power Query всичко се случва с кликане по менютата и накрая имаме създадена заявка, която може да бъде повторена (рефрешната) с натискането на един бутон. Техниката може да се приложи и при сравняване на адреси, имена, кодове в различни списъци и т.н.
Неприятната част е, че хората могат да бъдат много изобретателни, когато си кръщават файловете и е напълно възможно да получим двайсет файла, кръстени по 20 различни тертипа.
Донякъде това може да се контролира като към файловете изпратите и кратки указания, в които изрично уточните как трябва да се именуват файловете. В случая е достатъчно да запазят структурата “file Име Фамилия” и ако искат да добавят нещо – да използват за разделител интервал. Така впоследствие ще бъде лесно да ги обработите с Power Query.
Разбира се 100% гаранция няма, но повечето хора са съвестни и ще последват указанията. И ще останат двама-трима като Владо Кашона, които вари ги-печи ги – правят си каквото искат. Просто трябва да се приеме този риск.