Параметарисање путања података у Повер Куерију

Ако сте већ почели да користите алате бесплатног Повер Куери додатка у Мицрософт Екцел-у, онда ћете се врло брзо сусрести са једним високо специјализованим, али врло честим и досадним проблемом повезаним са сталним прекидом веза ка изворним подацима. Суштина проблема је у томе што ако се у вашем упиту позивате на спољне датотеке или фасцикле, онда Повер Куери чврсто кодира апсолутну путању до њих у тексту упита. На вашем рачунару све ради добро, али ако одлучите да пошаљете датотеку са захтевом својим колегама, онда ће они бити разочарани, јер. они имају другачији пут до изворних података на свом рачунару и наш упит неће радити.

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

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

Претпоставимо да имамо у фасцикли E:Извештаји о продаји лежи фајл 100 најбољих производа.клс, што је отпремање из наше корпоративне базе података или ЕРП система (1Ц, САП, итд.) Ова датотека садржи информације о најпопуларнијим робним артиклима и изгледа овако:

Вероватно је одмах одмах јасно да је скоро немогуће радити са њим у Екцел-у у овом облику: празни редови кроз један са подацима, спојене ћелије, додатне колоне, заглавље на више нивоа, итд. ће ометати.

Стога, поред ове датотеке у истој фасцикли, креирамо још једну нову датотеку Хандлер.клск, у којем ћемо креирати Повер Куери упит који ће учитавати ружне податке из изворне датотеке за отпремање 100 најбољих производа.клс, и ставите их у ред:

Прављење захтева за спољну датотеку

Отварање датотеке Хандлер.клск, изаберите на картици Датум команда Преузмите податке – из датотеке – из Екцел радне свеске (Подаци — Добијте податке — Из датотеке — Из Екцел-а), затим одредите локацију изворне датотеке и листа који нам је потребан. Изабрани подаци ће бити учитани у Повер Куери едитор:

Хајде да их вратимо у нормалу:

1. Избришите празне редове помоћу Почетна — Избриши линије — Избришите празне редове (Почетна — Уклони редове — Уклони празне редове).
2. Избришите непотребне горње 4 линије Почетна — Избриши редове — Избриши горње редове (Почетна — Уклони редове — Уклони горње редове).
3. Подигните први ред до заглавља табеле помоћу дугмета Користите први ред као заглавља табулатор Početna (Почетна — Користите први ред као заглавље).
4. Одвојите петоцифрени чланак од назива производа у другој колони помоћу команде подељена колона табулатор Трансформација (Трансформација — подељена колона).
5. Избришите непотребне колоне и преименујте наслове преосталих ради боље видљивости.

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

Остаје да ову оплемењену табелу вратимо на лист у нашој датотеци Хандлер.клск тим затворите и преузмите (Почетна — Затвори&Учитај) табулатор Početna:

Проналажење путање до датотеке у захтеву

Сада да видимо како наш упит изгледа „испод хаубе“, на интерном језику уграђеном у Повер Куери са сажетим именом „М“. Да бисте то урадили, вратите се на наш упит двоструким кликом на њега у десном окну Захтеви и везе и на картици преглед изабрати Напредни уређивач (Приказ — Напредни уређивач):

У прозору који се отвори, други ред одмах открива чврсто кодирану путању до наше оригиналне датотеке за отпремање. Ако можемо да заменимо овај текстуални стринг параметром, променљивом или везом до ћелије Екцел листа где је ова путања унапред написана, онда можемо лако да је променимо касније.

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

За сада затворимо Повер Куери и вратимо се на нашу датотеку Хандлер.клск. Хајде да додамо нови празан лист и направимо малу „паметну“ табелу на њој, у чију једину ћелију ће бити уписана пуна путања до наше изворне датотеке:

Да бисте креирали паметну табелу из редовног опсега, можете користити пречицу на тастатури Цтрл+T или дугме Форматирајте као табелу табулатор Početna (Почетна — Формат као табела). Наслов колоне (ћелија А1) може бити апсолутно било шта. Такође имајте на уму да сам ради јасноће дао име табели parametri табулатор Конструктор (Дизајн).

Копирање путање из Екплорера или чак ручно уношење у њу, наравно, није посебно тешко, али је најбоље минимизирати људски фактор и одредити путању, ако је могуће, аутоматски. Ово се може имплементирати помоћу стандардне функције Екцел радног листа ЋЕЛИЈА (МОБИЛНИ), који може дати гомилу корисних информација о ћелији наведеној као аргумент – укључујући путању до тренутне датотеке:

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

=ЛЕФТ(ЦЕЛЛ(“филенаме”);ФИНД(“[“;ЦЕЛЛ(“филенаме”))-1)&”Топ 100 продуцтс.клс”

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

=ЛЕФТ(ЦЕЛЛ(«филенаме»);ФИНД(«[«;ЦЕЛЛ(«филенаме»))-1)&»Топ-100 товаров.клс»

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

Параметризујте путању у упиту

Остаје последњи и најважнији додир – уписати путању до изворне датотеке у захтеву 100 најбољих производа.клс, који се односи на ћелију А2 наше креиране „паметне“ табеле parametri.

Да бисмо то урадили, вратимо се на Повер Куери упит и поново га отворимо Напредни уређивач табулатор преглед (Приказ — Напредни уређивач). Уместо текстуалног низа-путања у наводницима „Е: Салес репортс Топ 100 продуцтс.клск“ Хајде да представимо следећу структуру:

Екцел.ЦуррентВоркбоок(){[Наме=”Подешавања”]}[Садржај]0 {}[Путања до изворних података]

Да видимо од чега се састоји:

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

То је све, у ствари.

Остаје да кликнете на завршити и проверите како функционише наш захтев. Сада, када пошаљете цео фолдер са обе датотеке унутра на други рачунар, захтев ће остати оперативан и аутоматски ће одредити путању до података.

• Шта је Повер Куери и зашто је потребан када радите у Мицрософт Екцел-у
• Како да увезете плутајући исечак текста у Повер Куери
• Редизајнирање КСНУМКСД унакрсне табеле у равну табелу са Повер Куери-ом