Jak používat fuzzy matching v Power Query k čištění dat v Excelu

Standardní vyhledávání v Excelu jsou perfekcionisté, kteří selhávají při překlepů nebo nadbytečných mezerách. I když byste mohli vytvořit řešení pomocí kombinace funkcí XLOOKUP, TRIM a LOWER, je to křehké a obtížně udržovatelné. Fuzzy porovnávání v Power Query je chytřejší způsob, jak pracovat „dostatečně přesně“.

Power Query používá sofistikovaný algoritmus nepřesného porovnávání k výpočtu skóre podobnosti mezi dvěma řetězci, čímž překonává rozdíl mezi „Microsoft“ a „Mcrosoft“ bez jediného ručního zásahu. To znamená, že můžete během několika sekund sloučit neuspořádané exporty s čistšími hlavními databázemi.

Cíl: Příběh dvou stolů

Představte si, že máte prodejní zprávu (T_Sales, oranžová) plnou ručních záznamů a potřebujete získat prodejní region z hlavní tabulky (T_Master, zelená).

Avšak kvůli překlepům a různým konvencím pojmenování v T_Sales by standardní XLOOKUP vrátil #N/A téměř u všech vyhledávání. Proto potřebujeme, aby Excel prohlédl názvy v T_Sales, našel nejbližší shodu v T_Master a vrátil region, i když je pravopis trochu odlišný.

Aby bylo možné zpracovat specifické obchodní zkratky, které žádný algoritmus nedokáže uhodnout (jako „MSFT“), potřebujete také malou pomocnou tabulku (T_Abbrev, modrá) se sloupci From a To. Ujistěte se, že je tato tabulka připravena ještě před tím, než začnete.

Krok 1: Importujte svá data pomocí připojení

Než algoritmus pustíte do práce, musíte všechny tři tabulky načíst do Power Query. Začněte tím, že zajistíte, že vaše datové oblasti jsou formátovány jako tabulky v Excelu (Ctrl+T). Poté importujte první tabulku výběrem libovolné buňky v tabulce a kliknutím na Data > Z tabulky/rozsahu.

Aby se váš sešit nezaplnil, neklikejte po importu každé tabulky do Power Query na standardní tlačítko „Zavřít a načíst“. Místo toho klikněte na šipku dolů a vyberte možnost „Zavřít a načíst do...“.

Poté v dialogovém okně Importovat data vyberte možnost „Pouze vytvořit připojení“ a klikněte na „OK“.

Poté, co tento proces zopakujete pro všechny tři tabulky, uvidíte je pouze jako propojení v podokně Dotazy a propojení (které se otevře automaticky po vytvoření prvního propojení), připravené ke sloučení.

Krok 2: Sloučte své tabulky a začněte s čištěním

Chcete-li zahájit čištění, klikněte pravým tlačítkem na „T_Sales“ v podokně Dotazy a připojení a vyberte „Sloučit.“

Pak v dialogu Sloučit vyberte "T_Master" jako druhou tabulku a v náhledech obou tabulek vyberte sloupec "Name". V podstatě tím říkáte Excelu, že chcete propojit tabulky T_Sales a T_Master a sloupec Name je to, co je spojuje.

Nyní zaškrtněte „Použít přibližné porovnání pro provedení sloučení“, abyste Excelu dali najevo, že některá propojení nejsou dokonalá kvůli překlepům nebo nesrovnalostem v názvech. Ve výchozím nastavení je zapnutá volba Ignorovat velikost písmen, což znamená, že „MICROSOFT“ se správně spáruje s „microsoft“ bez dalších kroků. Co se týče typu spojení, držte se možnosti „Levé vnější“ — tímto zajistíte, že zachováte všechny prodeje a zároveň jednoduše přiřadíte odpovídající regionální informace.

Krok 3: Nastavte práh podobnosti

OK, takže jste Excelu řekli, které tabulky slučujete, jaká je propojující proměnná a že shody nejsou přesné. Teď musíte nastavit práh.

Klikněte na šipku vedle „Možnosti fuzzy shody.“

Prahová hodnota podobnosti je škála od 0,00 do 1,00, kde 0,00 odpovídá všem hodnotám s jakoukoliv úrovní podobnosti a 1,00 odpovídá pouze přesným hodnotám. Všimněte si na výše uvedeném snímku obrazovky, že tři ze šesti řádků jsou shodné. Je to proto, že Power Query má ve výchozím nastavení prahovou hodnotu podobnosti 0,80. Na této úrovni je engine přísný - snadno zachytí „Microsoft Inc“, ale selhává u více agresivních překlepů, jako jsou „Appel“ nebo „Gogle“.

V tomto případě je práh 0,50 dostatečně nízký na to, aby zachytil „Appel“ a „Gogle“, aniž by náhodou spároval nesouvisející slova. Najde pět z šesti řádků, přičemž zůstává nespárovaná pouze zkratka „MSFT“.

Krok 4: Použijte transformační tabulku pro zkratky

Žádný algoritmus není dost chytrý na to, aby pochopil, že „MSFT“ znamená Microsoft. Zde přichází na řadu tabulka T_Abbrev—v rozevíracím seznamu Transformation Table klikněte na „T_Abbrev“ a tím se přinutí engine, aby vaše ruční přepsání považoval za dokonalé shody. Jakmile ji vyberete, počet vašich shod ve spodní části se okamžitě zvýší na perfektní šest ze šesti.

Krok 5: Uklidit a shrnout vaši závěrečnou zprávu

Když kliknete na „OK“, otevře se Editor Power Query s novým sloupcem plným výsledků tabulky.

Klikněte na ikonu „Rozbalit“ v záhlaví sloupce, zrušte zaškrtnutí „Použít původní název sloupce jako předponu“, aby se předešlo neohrabaným záhlavím jako T_Master.Name, a klikněte na „OK.“

Můžete vidět, že názvy jsou nyní standardizovány ve sloupci Nový Název.1 a regiony jsou připojeny ve sloupci Region.

Odeberte původní nepořádný sloupec Jméno (klikněte pravým tlačítkem na záhlaví sloupce a vyberte „Odstranit“), potom klikněte a přetáhněte nový sloupec Jméno doleva, aby byl prvním sloupcem v tabulce, a přejmenujte ho (dvojitým kliknutím na záhlaví sloupce) na něco jako Oficiální název. Zároveň se ujistěte, že jsou každému sloupci přiřazeny správné datové typy, kliknutím na ikony v záhlaví sloupce.

Protože tento proces označuje každý jednotlivý obchod, skončíte s více řádky pro stejnou společnost. Chcete-li z toho vytvořit přehledný report, na kartě Transformace klikněte na „Seskupit podle.“

Poté v dialogovém okně Seskupit podle vyberte nahoře možnost „Pokročilé“. Tímto způsobem můžete seskupovat podle více sloupců najednou. V tomto případě chceme seskupit podle sloupců Název a Region. Pro první seskupení vyberte „Oficiální název“. Poté klikněte na „Přidat seskupení“ a vyberte „Region“.

Nyní nastavte výpočet. Do pole Nový název sloupce zadejte Celkový prodej, v poli Operace vyberte „Součet“ a v poli Sloupec vyberte „Prodeje“.

Když kliknete na „OK“, všechny opakující se řádky jsou seskupeny.

Jakmile zkontrolujete výsledky a potvrdíte, že všechna data se zobrazují podle očekávání, klikněte na „Zavřít a načíst“ na kartě Domů.

Nyní se vaše krásně shrnutá, dokonale napsaná data zobrazí v novém listu Excelu. Zde je screenshot před a po, který vám ukazuje výhodu použití fuzzy porovnávání v Power Query pro spojování a čištění neuspořádaných dat v Excelu.

Standardní vyhledávání je příliš rigidní pro realitu neuspořádaných dat. Použitím přibližného porovnávání vyměníte ruční zadávání dat za škálovatelný, automatizovaný systém, který chápe záměr, nikoli jen syntaxi. To je jen jeden z mnoha způsobů, jak využít nástroje Power Query k úpravě neuspořádaných dat v tabulkách - jakmile začnete, nebudete moci přestat!