Създаване на параметризирана заявка (Power Query)

Можете да използвате параметър, за да променяте автоматично стойност в заявка и да избягвате всеки път да редактирате заявката, за да промените стойността. Просто променяте стойността на параметъра. След като създадете параметър, той се записва в специална параметризирана заявка, която можете удобно да промените директно от Excel.

1. Изберете Данни > Получаване на данни > Други източници > стартиране на Редактор на Power Query.

2. В Редактор на Power Query изберете Начало > Управление на параметри > Нови параметри.

3. В диалоговия прозорец Управление на параметър изберете Създай.

4. Задайте следното, ако е необходимо:

   Име	Това трябва да отразява функцията на параметъра, но да е възможно най-кратка.
   Описание	Това може да съдържа всички подробности, които ще помогнат на хората правилно да използват параметъра.
   Задължително	Направете едно от следните неща: Всяка стойност Можете да въведете всяка стойност на всеки тип данни в параметризираната заявка. Списък със стойности    Можете да ограничите стойностите до конкретен списък, като ги въведете в малката мрежа. Трябва също да изберете стойност по подразбиране и текуща стойност по-долу. Заявка Изберете заявка за списък, която прилича на структурирана колона на списък , разделена със запетаи и оградена във фигурни скоби. Например едно поле "Състояние на проблемите" може да има три стойности: {"Създай", "Текущ", "Затворено"}. Трябва предварително да създадете заявката за списък, като отворите Разширен редактор (изберете Начало > Разширен редактор), премахнете шаблона за код, въведете списъка със стойности във формата на списъка със заявки и след това изберете Готово. След като приключите със създаването на параметъра, заявката за списък се показва във вашите стойности на параметри.
   Тип	Това указва типа на данните на параметъра.
   Предложени стойности	Ако желаете, добавете списък със стойности или задайте заявка, за да предоставите предложения за въвеждане.
   Стойност по подразбиране	Това се появява само ако "Предложени стойности" е зададено на "Списък със стойности" и задава кой елемент от списъка е елементът по подразбиране. В този случай трябва да изберете опция по подразбиране.
   Текуща стойност	В зависимост от това къде използвате параметъра, ако това е празно, заявката може да не върне резултати. Ако е избрано "Задължително", текущата стойност не може да е празна.

5. За да създадете параметъра, изберете OK.

Ето начин за управление на промените в местоположенията на източниците на данни и за предотвратяване на грешки при обновяване. Ако например приемем подобна схема и източник на данни, създайте параметър, за да промените лесно източник на данни и да предотвратите грешки при обновяване на данните. Понякога се променят сървърът, базата данни, папката, името на файла или местоположението. Може би диспечерът на бази данни понякога сменя сървър, месечната капка CSV файлове отива в друга папка или трябва лесно да превключвате между среда за разработване/тестване/производство.

Стъпка 1: Създаване на параметризирана заявка

В следващия пример имате няколко CSV файла, които импортирате с помощта на операцията за импортиране на папка (изберете Данни > Получаване на> данни от файлове > от папка) от папка C:\DataFilesCSV1. Но понякога друга папка понякога се използва като местоположение за пускане на файловете, C:\DataFilesCSV2. Можете да използвате параметър в заявка като заместваща стойност за друга папка.

1. Изберете Начало > Управление на параметри > Нов параметър.

2. Въведете следната информация в диалоговия прозорец Управление на параметър :

   Име	Csv ФайлОв фон
   Описание	Алтернативно местоположение за пускане на файлове
   Задължително	Да
   Тип	Текстово съобщение
   Предложени стойности	Всяка стойност
   Текуща стойност	C:\DataFilesCSV1

3. Изберете ОК.

Стъпка 2: Добавяне на параметъра към заявката за данни

1. За да зададете името на папката като параметър, в Настройки на заявката, под Стъпки на заявката изберете Източник и след това изберете Редактиране на настройките.

2. Уверете се, че опцията Път до файл е зададена на Параметър и след това изберете параметъра, който току-що създадохте, от падащия списък.

3. Изберете ОК.

Стъпка 3: Актуализиране на стойността на параметъра

Местоположението на папката току-що се промени, така че сега можете просто да актуализирате параметризираната заявка.

1. Изберете раздела Данни > връзки & заявки > заявки , щракнете с десния бутон върху параметризираната заявка и след това изберете Редактиране.

2. Въведете новото местоположение в полето Текуща стойност , като например C:\DataFilesCSV2.

3. Изберете Начало > Затваряне & Зареждане.

4. За да потвърдите резултатите, добавете нови данни към източника на данни и след това обновете заявката за данни с актуализирания параметър (Изберете данни > Обнови всички).

Понякога искате лесен начин да промените филтъра на заявка, за да получите различни резултати, без да редактирате заявката или да направите малко по-различни копия на една и съща заявка. В този пример променяме дата, за да променим удобно филтъра за данни.

1. За да отворите заявка, намерете такава, която преди това е заредена от Редактор на Power Query, изберете клетка в данните и след това изберете Заявка > Редактиране. За повече информация вижте Създаване, зареждане или редактиране на заявка в Excel.

2. Изберете стрелката за филтър в заглавката на някоя колона, за да филтрирате данните, и след това изберете команда за филтър, като например Филтри по дата/час > След. Появява се диалоговият прозорец Филтриране на редове .
   
   

3. Изберете бутона отляво на полето Стойност и след това направете едно от следните неща:

   • За да използвате съществуващ параметър, изберете Параметър и след това изберете желания параметър от списъка, който се появява вдясно.

   • За да използвате нов параметър, изберете Нов параметър и след това създайте параметър.

4. Въведете новата дата в полето Текуща стойност и след това изберете Начало > Затвори & Зареждане.

5. За да потвърдите резултатите, добавете нови данни към източника на данни и след това обновете заявката за данни с актуализирания параметър (Изберете данни > Обнови всички). Например променете стойността на филтъра на друга дата, за да видите новите резултати.

6. Въведете новата дата в полето Текуща стойност .

7. Изберете Начало > Затваряне & Зареждане.

8. За да потвърдите резултатите, добавете нови данни към източника на данни и след това обновете заявката за данни с актуализирания параметър (Изберете данни > Обнови всички).

В този пример стойността в параметъра на заявката се чете от клетка във вашата работна книга. Не е необходимо да променяте параметризираната заявка– просто актуализирате стойността на клетката. Например искате да филтрирате колона по първата буква, но лесно да промените стойността на всяка буква от А на Я.

1. В работния лист в работна книга, където е заредена заявката, която искате да филтрирате, създайте таблица на Excel с две клетки: горен колонтитул и стойност.
    

   MyFilter
   G,

2. Изберете клетка в таблицата на Excel, след което изберете Данни > Получаване на данни > От таблица/диапазон. Появява се Редактор на Power Query.

3. В полето Име на екрана Настройки на заявка отдясно променете името на заявката, за да бъде по-смислено, например FilterCellValue. 

4. За да предадете стойността в таблицата, а не самата таблица, щракнете с десния бутон върху стойността във Визуализация на данни и след това изберете Детайлизиране.

   Обърнете внимание, че формулата се е променила на = #"Changed Type"{0}[MyFilter]

   Когато използвате таблицата на Excel като филтър в стъпка 10, Power Query препраща към стойността на таблицата като условие за филтриране. Директна препратка към таблицата на Excel би причинила грешка.

5. Изберете Начало > Затвори & Зареждане > Затваряне & Зареждане в. Сега имате параметър на заявка с име "FilterCellValue", който използвате в стъпка 12.

6. В диалоговия прозорец Импортиране на данни изберете Само създаване на връзка и след това изберете OK.

7. Отворете заявката, която искате да филтрирате, със стойността в таблицата FilterCellValue, която преди това е заредила от Редактор на Power Query, като изберете клетка в данните и след това изберете Заявка > Редактиране. За повече информация вижте Създаване, зареждане или редактиране на заявка в Excel.

8. Изберете стрелката за филтър в някоя заглавка на колона, за да филтрирате данните, и след това изберете команда за филтър, като например Текстови филтри > Започва с. Появява се диалоговият прозорец Филтриране на редове . 

9. Въведете произволна стойност в полето Стойност , като например "G", и след това изберете OK. В този случай стойността е временен контейнер за стойността в таблицата FilterCellValue, която въвеждате в следващата стъпка.

10. Изберете стрелката от дясната страна на лентата за формули, за да покажете цялата формула. Ето пример за условие за филтриране във формула:
    
    = Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))

11. Изберете стойността на филтъра. Във формулата изберете "G".

12. Като използвате M Intellisense, въведете първите няколко букви на таблицата FilterCellValue, която сте създали, и след това я изберете от списъка, който се появява.

13. Изберете Начало > Затвори > Затвори & Зареждане.

Резултат

Вашата заявка сега използва стойността в таблицата на Excel, която сте създали, за да филтрирате резултатите от заявката. За да използвате нова стойност, редактирайте съдържанието на клетката в първоначалната таблица на Excel в стъпка 1, променете "G" на "V" и след това обновете заявката.

Можете да контролирате дали параметризираните заявки са разрешени, или не.

1. В Редактор на Power Query изберете Опции и настройки за > на файл > опции за заявки > Редактор на Power Query.

2. В екрана отляво, под ГЛОБАЛНИ изберете Редактор на Power Query.

3. В екрана отдясно, под Параметри изберете или изчистете отметката от Винаги позволявай параметризирай в диалоговите прозорци за източник на данни и трансформация.