Възможно е да сте запознати с параметризираните заявки с използването им в SQL или Microsoft Query. Параметрите на Power Query обаче имат и основни разлики:
- Параметрите могат да се използват във всяка стъпка на заявката. Освен че функционират като филтър за данни, параметрите могат да се използват и за задаване на неща като път към файл или име на сървър.
- Параметрите не изискват въвеждане. Вместо това можете бързо да промените стойността им с помощта на Power Query. Можете дори да съхранявате и извличате стойности от клетки в Excel.
- Параметрите се записват в проста параметризирана заявка, но са отделни от заявките за данни, в които се използват. След като го създадете, можете да добавяте параметър към заявките, както е необходимо.
Забележка Ако искате да използвате друг начин за създаване на параметризирани заявки, вижте "Създаване на параметризирана заявка" в Microsoft Query.
Създаване на параметър
Можете да използвате параметър, за да променяте автоматично стойност в заявка и да избегнете всяко редактиране на заявката за промяна на стойността. Трябва само да промените стойността на параметъра. След като създадете параметър, той се записва в специална параметризирана заявка, която можете удобно да промените директно от Excel.
Избор на данни>Получаване на данни>Други източници>стартирайте Редактор на Power Query.
В Редактор на Power Query изберете Начало>Управление на параметри > Нови параметри.
В диалоговия прозорец за управление на параметър изберете "Създай".
Задайте следните елементи, ако е необходимо:
Име Това трябва да отразява функцията на параметъра, но да е възможно най-кратко. Описание Той може да съдържа всякакви подробности, които ще помогнат на хората правилно да използват параметъра. Задължително Направете едно от следните неща:
Всяка стойност Можете да въведете всяка стойност от всеки тип данни в параметризираната заявка.
Списък със стойности Можете да ограничите стойностите до определен списък, като ги въведете в малка мрежа. Трябва също да изберете стойност по подразбиране и текуща стойност по-долу.
Заявка Изберете заявка към списък, която прилича на списъчна структурирана колона, разделена със запетаи и оградена във фигурни скоби.
Например едно поле за състояние "Проблеми" може да има три стойности: {"Нови", "Текущи", "Затворени"}. Трябва предварително да създадете заявката за списък, като отворите Разширен редактор (изберете HomeРазширен> редактор), премахнете шаблона за код, въведете списъка със стойности във формата на списъка със заявки и след това изберете "Готово".
След като завършите създаването на параметъра, заявката за списък се показва във вашите стойности на параметри.Тип Това задава типа на данните на параметъра. Предложени стойности Ако желаете, добавете списък със стойности или задайте заявка, за да предоставите предложения за въвеждане. Стойност по подразбиране Показва се само ако "Предложени стойности " е зададено като "Списък със стойности" и указва кой елемент от списъка е по подразбиране. В този случай трябва да изберете настройка по подразбиране. Текуща стойност В зависимост от това къде използвате параметъра, ако той е празен, заявката може да не върне никакви резултати. Ако е избрано "Задължително ", "Текуща стойност " не може да бъде празна. За да създадете параметъра, изберете OK.
Използване на параметър за промяна на източник на данни
Ето един начин да управлявате промените в местоположенията на източниците на данни и да предотвратите грешки при обновяване. Ако например приемете подобна схема и източник на данни, създайте параметър за лесна промяна на източник на данни и за предотвратяване на грешки при обновяване на данните. Понякога се променят сървърът, базата данни, папката, името на файла или местоположението. Може би мениджър на база данни от време на време сменя сървър, месечно падане на CSV файлове отива в друга папка или трябва лесно да превключвате между среда за разработка/тестване/производство.
Стъпка 1: Създаване на параметризирана заявка
В следващия пример имате няколко CSV файла, които импортирате чрез операцията за импортиране на папка (Select Data>Get>Data From FilesFrom>Folder) от папка C:\DataFilesCSV1. Но понякога се използва различна папка като местоположение за пускане на файловете – C:\DataFilesCSV2. Можете да използвате параметър в заявка като заместваща стойност за различната папка.
Изберете "Начална страница>","Управление на параметри>", "Нов параметър".
Въведете следната информация в диалоговия прозорец "Управление на параметър ":
Име CSVFileDrop Описание Алтернативно местоположение за пускане на файлове Задължително Да Тип Text Предложени стойности Всяка стойност Текуща стойност C:\DataFilesCSV1 Изберете OK.
Стъпка 2: Добавяне на параметъра към заявката за данни
- За да зададете името на папката като параметър, в настройките за заявка, под "Стъпки на заявката" изберете "Източник" и след това изберете "Редактиране на настройките".
- Уверете се, че опцията "Път до файл " е зададена на "Параметър", и след това изберете параметъра, който току-що създадохте, от падащия списък.
- Изберете OK.
Стъпка 3: Актуализиране на стойността на параметъра
Местоположението на папката току-що се промени, така че сега можете просто да актуализирате параметризираната заявка.
- Изберете връзките с данни>& раздела> "Заявки" щракнете с десния бутон върху параметризираната заявка и след това изберете "Редактиране".
- Въведете новото местоположение в полето за текуща стойност , като например C:\DataFilesCSV2.
- Изберете "Начало>", "Затвори" & "Зареди".
- За да потвърдите вашите резултати, добавете нови данни към източника на данни и след това обновете заявката за данни с актуализирания параметър (Select>Data Refresh All).
Използване на параметър за филтриране на данни
Понякога искате лесен начин да промените филтъра на заявка, за да получите различни резултати, без дори да редактирате заявката или да правите малко по-различни копия на една и съща заявка. В този пример променяме дата, за да променим удобно филтър за данни.
За да отворите заявка, намерете предварително заредена заявка от Редактор на Power Query, изберете клетка в данните и след това изберете "Редактиране на заявка>". За повече информация вижте "Създаване, зареждане или редактиране на заявка в Excel".
Изберете стрелката за филтриране в някоя заглавка на колона, за да филтрирате данните, и след това изберете команда за филтриране, като например "Филтри> за дата и час" след. Появява се диалоговият прозорец "Филтриране на редове ".
Изберете бутона отляво на полето за стойност и след това направете едно от следните неща:
- За да използвате съществуващ параметър, изберете "Параметър" и след това изберете желания параметър от списъка, който се появява вдясно.
- За да използвате нов параметър, изберете "Нов параметър" и след това създайте параметър.
Въведете новата дата в полето "Текуща стойност " и след това изберете "Начало>" Затвори & Зареди.
За да потвърдите вашите резултати, добавете нови данни към източника на данни и след това обновете заявката за данни с актуализирания параметър (Select>Data Refresh All). Например променете стойността на филтъра на различна дата, за да видите новите резултати.
Въведете новата дата в полето "Текуща стойност ".
Изберете "Начало>", "Затвори" & "Зареди".
За да потвърдите вашите резултати, добавете нови данни към източника на данни и след това обновете заявката за данни с актуализирания параметър (Select>Data Refresh All).
Използване на стойност на клетка за филтриране на данни
В този пример стойността в параметъра на заявката се чете от клетка във вашата работна книга. Не е необходимо да променяте параметризираната заявка, просто актуализирате стойността на клетката. Например искате да филтрирате колона по първата буква, но лесно да промените стойността й във всяка буква от А до Я.
В работния лист в работната книга, където е заредена заявката, която искате да филтрирате, създайте таблица на Excel с две клетки: заглавка и стойност.
Моят филтър G Изберете клетка в таблицата на Excel, след което изберете "Данни>" Получаване на данни>от таблица/диапазон. Появява се Редактор на Power Query.
В полето " Име " на екрана " Настройки за заявка " отдясно променете името на заявката, за да бъде по-смислено, например FilterCellValue.
За да предадете стойността в таблицата, а не самата таблица, щракнете с десния бутон върху стойността във "Визуализация на данни" и след това изберете "Детайлизиране".
Обърнете внимание, че формулата се промени на= #"Changed Type"{0}[MyFilter]
Когато използвате таблицата на Excel като филтър в стъпка 10, Power Query препраща към стойността на таблицата като условие за филтриране. Директна препратка към таблицата на Excel би довела до грешка.Изберете "Начало>","Затваряне" & "Зареждане>","Затваряне" & "Зареждане в". Сега имате параметър на заявката с име "FilterCellValue", която използвате в стъпка 12.
В диалоговия прозорец за импортиране на данни изберете "Само създаване на връзка" и след това изберете OK.
Отворете заявката, която искате да филтрирате със стойността в таблицата FilterCellValue, предварително заредена от Редактор на Power Query, като изберете клетка в данните и след това изберете "Редактиране на> заявка". За повече информация вижте "Създаване, зареждане или редактиране на заявка в Excel".
Изберете стрелката за филтриране в произволна заглавка на колона, за да филтрирате данните, и след това изберете команда за филтриране, като например "Текстови филтри>започва с". Появява се диалоговият прозорец "Филтриране на редове ".
Въведете произволна стойност в полето " Стойност ", като например "G", и след това изберете OK. В този случай стойността е временен контейнер за стойността в таблицата FilterCellValue, която въвеждате в следващата стъпка.
Изберете стрелката от дясната страна на лентата за формули, за да покажете цялата формула. Ето пример за условие за филтриране във формула:
= Table.SelectRows(#"Changed Type", each Text.StartsWith([Name], "G"))
Изберете стойността на филтъра. Във формулата изберете "G".
Използвайки M Intellisense, въведете първите няколко букви на таблицата FilterCellValue, която създадохте, и след това я изберете от списъка, който се появява.
Изберете "Начало>", "Затвори>", "Затвори" & "Зареждане".
Резултат
Вашата заявка сега използва стойността в таблицата на Excel, която сте създали, за да филтрира резултатите от заявката. За да използвате нова стойност, редактирайте съдържанието на клетката в оригиналната таблица на Excel в стъпка 1, сменете "G" с "V" и след това обновете заявката.
Управление на използването на параметризирани заявки
Можете да контролирате дали параметризираните заявки са разрешени, или не.
- В Редактор на Power Query изберете Опции за файл>и настройки>Опции за> заявка Редактор на Power Query.
- В екрана вляво, под GLOBAL, изберете Редактор на Power Query.
- В десния екран под "Параметри" поставете или изчистете отметката от "Винаги позволявай параметризиране в диалоговите прозорци за източник на данни и трансформация".