Създаване на формули на Power Query в Excel

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

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

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

3. Контекстни менюта, които са удобни преки пътища до командите на лентата

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

5. The Query Settings pane that lists properties and each step in the query

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

Може да има случаи, когато искате да промените или да създадете формула. Формулите използват езика за формули на 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. За да запишете заявката, изберете Начало > Затвори & Зареждане.

Result

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

Стъпка и задача за заявка	Формула
Източник Свързване към уеб източник на данни	= 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 да дефинират и настроят източника на данни.

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

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

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

Добавянена формула в лентата за формули

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

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

3. В лентата за формули намерете и променете стойностите на параметрите, след което изберете иконата Enter или натиснете клавиша Enter. Например променете тази формула, за да запазите и "Колона2:
   
   Преди: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   след:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

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

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

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

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

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

2. В лентата за формули въведете=Text.Proper("text value")и след това изберете иконата Enter или натиснете клавиша 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])
   
   Да предположим например, че имате таблица с колоната Gender и искате да добавите колона със стойността "Ms". или "Г-н", в зависимост от пола на лицето. Формулата би била:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

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

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

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

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

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

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

Преди:

След:

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

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

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

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

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

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

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

1. Създайте израза let с помощта на функцията Excel.CurrentWorkbook по следния начин:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   Source#x4

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

Резултат:

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

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

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

3. В израза let добавете функцията #"First Row as Header" и Table.PromoteHeaders по следния начин:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #x4#"First Row as Header" = Table.PromoteHeaders(Source)#x3
   

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

Резултат:

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

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

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

3. В израза let конвертирайте всяка стойност на колоната ProductName в подходящ текст с помощта на функцията Table.TransformColumns, препращайки към предишната стъпка на формулата на заявка "Първи ред като заглавка", като добавите #"Capitalized Each Word" към източника на данни и след това задайте #"Capitalized Each Word" към резултата.
   
   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. За да заредите заявката в работен лист, изберете Готово и след това изберете Начало > Затвори & Зареждане > Затваряне & Зареждане.

Резултат:

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

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

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

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

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

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

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

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

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

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