Параметризирање на патеките на податоци во Power Query

Ако веќе сте почнале да ги користите алатките на бесплатниот додаток Power Query во Microsoft Excel, тогаш многу наскоро ќе наидете на еден високо специјализиран, но многу чест и досаден проблем поврзан со постојано кршење врски до изворните податоци. Суштината на проблемот е во тоа што ако во вашето барање се однесувате на надворешни датотеки или папки, тогаш Power Query ја шифрира апсолутната патека до нив во текстот на барањето. Сè работи добро на вашиот компјутер, но ако одлучите да испратите датотека со барање до вашите колеги, тогаш тие ќе бидат разочарани, бидејќи. тие имаат поинаков пат до изворните податоци на нивниот компјутер и нашето барање нема да работи.

Што да направите во таква ситуација? Да го разгледаме овој случај подетално со следниот пример.

Формулирање на проблемот

Да претпоставиме дека имаме во папката E:Извештаи за продажба лежи датотеката Топ 100 производи.xls, што е поставено од нашата корпоративна база на податоци или ERP систем (1C, SAP, итн.) Оваа датотека содржи информации за најпопуларните стоки и изгледа вака внатре:

Веројатно веднаш е јасно дека е речиси невозможно да се работи со него во Excel во оваа форма: празните редови низ еден со податоци, споени ќелии, дополнителни колони, заглавие на повеќе нивоа итн. ќе пречат.

Затоа, веднаш до оваа датотека во истата папка, создаваме друга нова датотека Ракувач.xlsx, во кој ќе создадеме барање за Power Query што ќе вчита грди податоци од изворната датотека за прикачување Топ 100 производи.xls, и подреди ги:

Поставување барање до надворешна датотека

Отворање на датотеката Ракувач.xlsx, изберете на јазичето податоци Команда Добијте податоци – од датотека – од работна книга на Excel (Податоци - Добијте податоци - Од датотека - Од Excel), потоа наведете ја локацијата на изворната датотека и листот што ни треба. Избраните податоци ќе се вчитаат во уредувачот на Power Query:

Да ги вратиме во нормала:

1. Избришете празни линии со Дома — Избриши линии — Избришете празни линии (Дома - Отстрани редови - Отстрани празни редови).
2. Избришете ги непотребните горни 4 линии Дома — Избриши редови — Избриши горните редови (Дома - Отстрани редови - Отстрани горните редови).
3. Подигнете го првиот ред до заглавието на табелата со копчето Користете ја првата линија како заглавија табот Почетна (Дома - Користете го првиот ред како заглавие).
4. Одделете ја петцифрената статија од името на производот во втората колона користејќи ја командата поделена колона табот Трансформација (Трансформација - поделена колона).
5. Избришете ги непотребните колони и преименувајте ги насловите на останатите за подобра видливост.

Како резултат на тоа, треба да ја добиеме следната, многу попријатна слика:

Останува да ја подигнеме оваа облагородена табела назад во листот во нашата датотека Ракувач.xlsx Тимот затворете и преземете (Дома - Затвори&Вчитај) табот Почетна:

Наоѓање на патеката до датотека во барање

Сега да видиме како нашето барање изгледа „под капакот“, на внатрешниот јазик вграден во Power Query со концизното име „М“. За да го направите ова, вратете се на нашето барање со двојно кликнување на него во десниот панел Барања и врски и на јазичето преглед изберете Напреден уредник (Преглед - Напреден уредник):

Во прозорецот што се отвора, втората линија веднаш открива тврдо-кодирана патека до нашата оригинална датотека за подигнување. Ако можеме да ја замениме оваа текстуална низа со параметар, променлива или врска до ќелија на лист на Excel каде оваа патека е претходно напишана, тогаш лесно можеме да ја промениме подоцна.

Додајте паметна табела со патека на датотека

Ајде да го затвориме Power Query засега и да се вратиме на нашата датотека Ракувач.xlsx. Ајде да додадеме нов празен лист и да направиме мала „паметна“ табела на неа, во единствената ќелија од која ќе биде напишана целосната патека до нашата изворна датотека со податоци:

За да креирате паметна табела од редовен опсег, можете да ја користите кратенката на тастатурата Ctrl+T или копче Форматирајте како табела табот Почетна (Дома - Формат како табела). Насловот на колоната (ќелија А1) може да биде апсолутно сè. Исто така, забележете дека за јасност и дадов име на табелата параметри табот Изведувач (Дизајн).

Копирањето патека од Explorer или дури и рачното внесување, се разбира, не е особено тешко, но најдобро е да се минимизира човечкиот фактор и да се одреди патеката, ако е можно, автоматски. Ова може да се имплементира со користење на стандардната функција за работни листови на Excel ЦЕЛ (ELLЕЛИЈА), кој може да даде куп корисни информации за ќелијата наведена како аргумент - вклучувајќи ја патеката до тековната датотека:

Ако претпоставиме дека датотеката со изворни податоци секогаш лежи во истата папка како и нашиот процесор, тогаш патеката што ни треба може да се формира со следнава формула:

=LEFT(CELL(„име на датотека“);FIND(„[“;CELL(„име на датотека“))-1)&“Топ 100 производи.xls“

или во англиска верзија:

=LEFT(CELL(«име на датотека»);FIND(«[«;CELL(«име на датотека»))-1)&»Топ-100 товаров.xls»

… каде е функцијата ЛЕВСИМВ (ЛЕВО) зема парче текст од целосната врска до почетната квадратна заграда (т.е. патеката до тековната папка), а потоа на неа се залепени името и наставката на нашата изворна датотека со податоци.

Параметризирајте ја патеката во барањето

Останува последниот и најважен допир - да се напише патеката до изворната датотека во барањето Топ 100 производи.xls, повикувајќи се на ќелијата А2 од нашата креирана „паметна“ табела параметри.

За да го направите ова, да се вратиме на барањето Power Query и да го отвориме повторно Напреден уредник табот преглед (Преглед - Напреден уредник). Наместо текстуална низа-пат во наводници „Е: Извештаи за продажба Топ 100 производи.xlsx“ Да ја воведеме следната структура:

Excel.Current Workbook(){[Name=”Settings”]}[Содржина]0 {}[Патот до изворните податоци]

Ајде да видиме од што се состои:

• Excel.Current Workbook() е функција на јазикот М за пристап до содржината на тековната датотека
• {[Name=”Settings”]}[Содржина] – ова е параметар за усовршување на претходната функција, што покажува дека сакаме да ја добиеме содржината на „паметната“ табела параметри
• [Патот до изворните податоци] е името на колоната во табелата параметрина кои се повикуваме
  
• 0 {} е бројот на редот во табелата параметриод кои сакаме да земеме податоци. Капчето не се брои и нумерирањето започнува од нула, а не од една.

Тоа е сè, всушност.

Останува да кликнете на Заврши и проверете како функционира нашето барање. Сега, кога ја испраќате целата папка со двете датотеки внатре на друг компјутер, барањето ќе остане оперативно и автоматски ќе ја одреди патеката до податоците.

• Што е Power Query и зошто е потребно кога работите во Microsoft Excel
• Како да увезете пловечки фрагмент од текст во Power Query
• Редизајнирање на XNUMXD вкрстено јазиче на рамна табела со барање за напојување