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

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

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

1. В раздела на лентата POWER QUERY изберете От други източници > Празна заявка.

   

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

3. Power Query ви показва резултатите в екрана с резултатите от формулата.

4. За да видите резултата в работен лист на Excel, изберете Затвори и зареди.

Резултатът ще изглежда по следния начин в работен лист:

В редактора на заявки можете също да създавате и разширени формули на заявки.

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

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

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

Първоначалната таблица изглежда по следния начин:

А сега искате получената таблица да изглежда по следния начин:

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

Разширена заявка с използване на пример с разширения редактор

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

• Създайте серия стъпки на формулата за заявка, които започват с оператора let. Обърнете внимание, че в езика за формули на Power Query се прави разлика между главни и малки букви.

• Всяка стъпка на формулата за заявка се обръща към предишната стъпка по име.

• Извеждането на стъпка на формулата за заявка става с оператора in. Като правило, последната стъпка на заявката се използва като окончателен резултат за набора данни на in.

Стъпка 1 – Отваряне на разширения редактор

1. В раздела на лентата POWER QUERY изберете От други източници > Празна заявка.

2. В Редактор на заявки изберете Разширен редактор.

   

3. Ще видите разширения редактор.

   

Стъпка 2 – Дефиниране на първоначалния източник

В разширения редактор:

1. Използвайте оператор, който присвоява формула Source = Excel.CurrentWorkbook(). Тук ще се използва таблица на Excel като източник на данни. За повече информация за формулата Excel.CurrentWorkbook() вижте Excel.CurrentWorkbook.

2. Задайте Source на резултата от in.

   let Source =
   Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in Source

3. Вашата разширена заявка ще изглежда в разширения редактор по следния начин:

   

4. За да видите резултатите в работен лист:

   1. Щракнете върху Готово.

   2. В лентата на редактора на заявки щракнете върху Затвори и зареди.

Резултатът в работен лист ще изглежда по следния начин:

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

За да преобразувате стойностите в колоната ProductName да започват с главна буква, трябва първо да повишите първия ред, за да стане ред със заглавки на колони. Можете да направите това в разширения редактор:

1. Добавете формула #"First Row as Header" = Table.PromoteHeaders() към стъпките на заявката си и посочете Source като източник на данни. За повече информация за формулата Table.PromoteHeaders() вижте Table.PromoteHeaders.

2. Задайте #"First Row as Header" на резултата от in.

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

Резултатът в работен лист ще изглежда по следния начин:

Стъпка 4 – Промяна на всяка стойност в дадена колона да започва с главна буква

За да конвертирате всяка стойност на колоната ProductName да започва с главна буква, можете да използвате Table.TransformColumns() и да посочите стъпката "First Row as Header” на формулата за заявка. Можете да направите това в разширения редактор:

1. Добавете формула #"Capitalized Each Word" = Table.TransformColumns() към своите стъпки на формулата за заявка и посочете #"First Row as Header" като източник на данни. За повече информация за формулата Table.TransformColumns() вижте Table.TransformColumns.

2. Задайте #"Capitalized Each Word" на резултата от in.

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"

Крайният резултат ще промени всяка стойност в колоната ProductName да започва с главна буква и да изглежда по следния начин в работен лист:

С езика за формули на Power Query можете да създавате от прости до разширени заявки за данни, за да откривате, комбинирате и прецизирате данни. За да научите повече за Power Query, вижте Помощ за Microsoft Power Query за Excel.