Само с помощта на редактора на Power Query вие създавате формули на Power Query през цялото време. Нека да видим как работи Power Query, като търсим под капака. Можете да научите как да актуализирате или добавяте формули само като гледате редактора на Power Query в действие.  Можете дори да търкаляте собствени формули с разширения редактор.           

Редакторът на Power Query предоставя заявка за данни и оформяне за Excel, които можете да използвате за преоформяне на данни от много източници на данни. За да покажете прозореца на редактора на Power Query,импортирайте данни от външни източници на данни в работен лист на Excel, изберете клетка в данните и след това изберете Заявка > Редактиране. По-долу е резюме на основните компоненти.

Части на редактора на заявки

  1. Лентата на редактора на power Query, която използвате за оформяне на вашите данни

  2. Екранът "Заявки", който използвате за намиране на източници на данни и таблици

  3. Контекстни менюта, които са удобни клавишни комбинации към команди в лентата

  4. Визуализация на данни, която показва резултатите от стъпките, приложени към данните

  5. Екранът заявка Настройки, който изброява свойствата и всяка стъпка в заявката

Зад кулисите всяка стъпка в заявка се базира на формула, която е видима в лентата за формули.

Пример за формула в редактора на заявки

Може да има случаи, когато искате да промените или създадете формула. Формулите използват езика на формулите на Power Query, който можете да използвате, за да създадете както прости, така и сложни изрази. За повече информация относно синтаксиса, аргументите, забележките, функциите и примеритевж. Език на формулата на Power Query M.

Като използвате списък с футболни първенства като пример, използвайте Power Query, за да вземете необработени данни, които сте намерили в уеб сайт, и да го превърнете в добре форматирана таблица. Гледайте как се създават стъпките на заявката и съответните формули за всяка задача в екрана Заявка Настройки под Приложени стъпки и в лентата за формули.

Вашият браузър не поддържа видео.

Процедура

  1. За да импортирате данните, изберете Данни> От уеб, въведете "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" в полето URL адрес и след това изберете OK.

  2. В диалоговия прозорец Навигатор изберете таблицата Резултати [Редактиране] отляво и след това изберете Трансформиране на данни в долната част. Появява се редакторът на Power Query.

  3. За да промените името на заявката по подразбиране, в екрана Заявка Настройки, под Свойства изтрийте"Резултати [Редактиране]" и след това въведете "UEFA champs".

  4. За да премахнете нежелани колони, изберете първата, четвъртата и петата колони и след това изберете Начало > Премахване на > Премахване на други колони.

  5. За да премахнете нежелани стойности, изберете Колона1, изберете Начало > Заместванена стойности , въведете "подробни данни" в полето Стойности за търсене и след това изберете OK.

  6. За да премахнете редове, които имат думата "Година" в тях, изберете стрелката за филтриране в колона1, изчистете отметката от квадратчето до "Година", след което изберете OK.

  7. За да преименувате заглавките на колоните, щракнете двукратно върху всеки от тях и след това променете "Колона1" на "Година", "Колона4" на "Победител" и "Колона5" на "Окончателен резултат".

  8. За да запишете заявката, изберете Начало> Затвори & Зареждане.

Резултат

Резултати от напредъка – първите няколко реда

Следващата таблица е обобщение на всяка приложена стъпка и съответната формула.

Стъпка и задача на заявката

Формула

Източник

Свързване към уеб източник на данни

= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))

Навигация

Изберете таблицата за свързване

=Source{2}[Data]

Променен тип

Промяна на типовете данни (което Power Query прави автоматично)

= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})

Премахнати са други колони

Премахване на другите колони, за да се показват само важните колони

= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})

Заместена стойност

Заместване на стойности за изчистване на стойности в избрана колона

= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})

Филтрирани редове

Филтриране на стойности в колона

= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))

Преименувани колони

Променени са заглавките на колоните, за да бъдат смислени

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Важно    Внимавайте да редактирате стъпките Източник, Навигацияи Променен тип, тъй като те са създадени от   Power Query, за да дефинирате и настроите източника на данни.

Показване или скриване на лентата за формули

Лентата за формули се показва по подразбиране, но ако не е видима, можете да я дисплеите отново.

  • Изберете Изглед >оформление > лентата за формули.

Edit a formula in the formula bar

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

  2. В екрана Заявка Настройки, под Приложени стъпкиизберете стъпката, която искате да редактирате.

  3. В лентата за формули намерете и променете стойностите на параметрите и след това изберете иконата Enter Иконата Enter отляво на лентата за формули в Power Query или натиснете Enter. Например променете тази формула, за да запазите колона2:

    Преди: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
    След:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. Изберете иконата Enter Иконата Enter отляво на лентата за формули в Power Query или натиснете Enter, за да видите новите резултати, показани във визуализацията на данни.

  5. За да видите резултата в работен лист Excel, изберете Начало> Затвори & Зареждане.

Създаване на формула в лентата за формули

За пример за проста формула нека преобразуваме текстова стойност в правилен случай с помощта на функцията Text.Proper.

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

  2. В лентата за формули въведете=Text.Proper("text value")и след това изберете иконата Enter Иконата Enter отляво на лентата за формули в Power Query или натиснете Enter.

    Резултатите се показват във "Визуализация на данни".

  3. За да видите резултата в работен лист Excel, изберете Начало> Затвори & Зареждане.

Резултат:

Избор на анимацията, която искате да се активира

 Когато създавате формула, Power Query проверява синтаксиса на формулата. Когато обаче вмъквате, пренаредите или изтриете междинна стъпка в заявка, може потенциално да прекъснете заявка.  Винаги проверявайте резултатите във "Визуализация на данни".

Важно    Внимавайте да редактирате стъпките Източник, Навигацияи Променен тип, тъй като те са създадени от   Power Query, за да дефинирате и настроите източника на данни.

Редактиране на формула с помощта на диалогов прозорец

Този метод използва диалогови прозорци, които се различават в зависимост от стъпката. Не е нужно да знаете синтаксиса на формулата.

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

  2. В екрана Заявка Настройки, под Приложени стъпки изберетеиконата Редактиране Настройки Икона за настройки на стъпката, която искате да редактирате, или щракнете с десния бутон върху стъпката, след което изберете Редактиране Настройки.

  3. В диалоговия прозорец направете промените и след това изберете OK.

Вмъкване на стъпка

След като изпълните стъпка от заявка, която преоформя данните ви, под текущата стъпка на заявката се добавя стъпка от заявката. но когато вмъквате стъпка от заявка в средата на стъпките, може да възникне грешка в следващите стъпки. Power Query показва предупреждение "Вмъкване на стъпка", когато се опитате да вмъкнете нова стъпка, а новата стъпка променя полета, като например имена на колони, които се използват в някоя от стъпките, които следват вмъкнатата стъпка.

  1. В екрана Заявка Настройки, под Приложени стъпкиизберете стъпката, която искате да предхождате непосредствено новата стъпка и съответната й формула.

  2. Изберете иконата Добавяне Икона "Функция" стъпка отляво на лентата за формули. Като алтернатива щракнете с десния бутон върху стъпка и след това изберете Вмъкване на стъпка след.Създава се нова формула във формата:

    = <nameOfTheStepToReference>, като например =Production.WorkOrder.

  3. Въведете новата формула с помощта на формата:

    =Class.Function(ReferenceStep[,otherparameters])

    Да предположим например, че имате таблица с колоната "Пол" и искате да добавите колона със стойността "Ms". или "Г-н", в зависимост от пола на лицето. Формулата ще бъде:

    =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Примерна формула

Пренаредка на стъпка

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

Изтриване на стъпка

  • Изберете иконата Изтриване на стъпка изтриване отляво на стъпката или щракнете с десния бутон върху стъпката и след това изберете Изтрий или Изтрий до края. Иконата Изтриване на стъпка изтриване също е налична отляво на лентата за формули.

В този пример нека преобразуваме текста в колона в подходящ случай с помощта на комбинация от формули в разширения редактор. 

Например имате таблица с Excel, наречена "Поръчки", с колона ProductName, която искате да преобразувате в правилен случай. 

Преди:

Преди

След:

Стъпка 4 – резултат

Когато създавате разширена заявка, създавате серия от стъпки за формули за заявки въз основа на израза let.  Използвайте израза let, за да присвоите имена и да изчислите стойности, към които след това се прави препратка от клаузата in, която дефинира стъпката. Този пример връща същия резултат като този в секцията "Създаване на формула в лентата за формули".

let  
    Source = Text.Proper("hello world")
in  
    Source  

Ще видите, че всяка стъпка се основава на предишна стъпка, като се обърнете към стъпка по име. Като напомняне езикът на формулата на Power Query е с малки и главни букви.

Фаза 1: Отваряне на разширения редактор

  1. В Excel изберете Данни> получаване на > други източници > празна заявка. За повече информация вижте Създаване, зареждане или редактиране на заявка в Excel.

  2. В редактора на Power Query изберете Home> Advanced Editor, който се отваря с шаблон на израза let.

Отчет за служители във "Визуализация на печата"

Фаза 2: Дефиниране на източника на данни

  1. Създайте израза let using the Excel. CurrentWorkbook функция, както следва:

    let#x1

    in
        Source

    Диалоговият прозорец ''Мащабиране''.

  2. За да заредите заявката в работен лист, изберете Готово ислед това изберете Начало> Затвори & Зареждане > Затвори & Зареждане.

Резултат:

Математически символ

Фаза 3: Повишаване на първия ред в заглавки

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

  2. В редактора на Power Query изберете Home > Advanced Editor, който се отваря с командата, която сте създали във фаза 2: Дефиниране на източника на данни.

  3. В израза let add #"First Row as Header" and Table.PromoteHeaders функция, както следва:

    let
        
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   #"First Row as Header" = Table.PromoteHeaders(Source)#x3

        #"First Row as Header"

  4. За да заредите заявката в работен лист, изберете Готово ислед това изберете Начало> Затвори & Зареждане > Затвори & Зареждане.

Резултат:

Стъпка 3 – резултат

Фаза 4: Промяна на всяка стойност в колона на правилен случай

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

  2. В редактора на Power Query изберете Home > Advanced Editor, който се отваря с командата, която сте създали във фаза 3: Повишаване на първия ред в заглавки.

  3. В израза let преобразувайте всяка стойност на колоната ProductName в правилен текст с помощта на функцията Table.TransformColumns, препращаща към предишната стъпка от формулата "Първи ред като горен колонтитул", добавяне на #"Капитализирано всяка дума" към източника на данни и след това присвояване на #"Капитализирано всяка дума" към резултата.

    let
        Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
        #"First Row as Header" = Table.PromoteHeaders(Source),
        #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
    in
        #"Capitalized Each Word"

  4. За да заредите заявката в работен лист, изберете Готово ислед това изберете Начало> Затвори & Зареждане > Затвори & Зареждане.

Резултат:

Стъпка 4 – резултат

Можете да контролирате поведението на лентата за формули в редактора на Power Query за всички работни книги.

Показване или скриване на лентата за формули

  1. Изберете Опции>файл и опции Настройки > за заявки.

  2. В левия екран, под GLOBALизберете Редактор на Power Query.

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

Включване или изключване на M Intellisense

  1. Изберете Опции>файл и опции Настройки > за заявки .

  2. В левия екран, под GLOBALизберете Редактор на Power Query.

  3. В десния екран, под Формулаизберете или изчистете разрешаването на M Intellisenseв лентата за формули, разширения редактор и диалоговия прозорец за колона по избор.

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

Вижте също

Помощ за Power Query Excel Power Query

Създаване и извикване на потребителска функция

Използване на списъка "Приложени стъпки" (docs.com)

Използване на функции по избор (docs.com)

Формули на Power Query M (docs.com)

Справяне с грешки (docs.com)

Нуждаете се от още помощ?

Разширете уменията си
Преглед на обучението
Получавайте първи новите функции
Присъединяване към Microsoft Office участници в Insider

Беше ли полезна тази информация?

Доколко сте доволни от качеството на езика?
Какво е повлияло на вашия потребителски опит?

Благодарим ви за обратната връзка!

×