Crearea de formule Power Query în Excel

Editor Power Query furnizează o experiență de interogare și modelare a datelor pentru Excel pe care o puteți utiliza pentru a modela date din multe surse de date. Pentru a afișa fereastra Editor Power Query, importați date din surse de date externeîntr-o foaie de lucru Excel, selectați o celulă din date, apoi selectați Interogare > Editare. Iată un rezumat al componentelor principale.

1. Panglica Editor Power Query pe care o utilizați pentru a vă modela datele

2. Panoul Interogări pe care îl utilizați pentru a găsi surse de date și tabele

3. Meniuri contextuale care sunt comenzi rapide convenabile la comenzile din panglică

4. Previzualizarea datelor care afișează rezultatele pașilor aplicați la date

5. Panoul Setări interogare care listează proprietățile și fiecare pas din interogare

În culise, fiecare pas dintr-o interogare se bazează pe o formulă care este vizibilă în bara de formule.

Pot exista momente când doriți să modificați sau să creați o formulă. Formulele utilizează Power Query Formula Language, pe care o puteți utiliza pentru a construi expresii simple și complexe. Pentru mai multe informații despre sintaxă, argumente, observații, funcții și exemple, consultați Power Query limbaj pentru formule M.

Utilizând o listă de campionate de fotbal ca exemplu, utilizați Power Query pentru a lua date brute pe care le-ați găsit pe un site web și a le transforma într-un tabel bine formatat. Urmăriți cum sunt creați pașii de interogare și formulele corespunzătoare pentru fiecare activitate în panoul Setări interogare , sub Pași parcurși și în bara Formule.

Procedură

1. Pentru a importa datele, selectați Date > De pe web, introduceți "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" în caseta URL , apoi selectați OK.

2. În caseta de dialog Navigator , selectați tabelul Rezultate [Editare] din stânga, apoi selectați Transformare date în partea de jos. Apare editorul Power Query.

3. Pentru a modifica numele interogării implicite, în panoul Setări interogare , sub Proprietăți, ștergeți "Rezultate [Editare]" și introduceți "Campioni UEFA".

4. Pentru a elimina coloanele nedorite, selectați prima, a patra și a cincea coloană, apoi selectați Pornire > Eliminare coloană > Eliminare alte coloane.

5. Pentru a elimina valorile nedorite, selectați Coloană1, selectați Pornire > Înlocuire valori, introduceți "detalii" în caseta Valori de găsit, apoi selectați OK.

6. Pentru a elimina rândurile care conțin cuvântul "An", selectați săgeata de filtrare din Coloana1, debifați caseta de selectare de lângă "An", apoi selectați OK.

7. Pentru a redenumi anteturile de coloană, faceți dublu clic pe fiecare dintre ele, apoi modificați "Coloana1" în "An", "Coloana4" în "Câștigător" și "Coloana5" în "Punctaj final".

8. Pentru a salva interogarea, selectați Pornire > Închidere & Încărcare.

Rezultat

Următorul tabel este un rezumat al fiecărui pas aplicat și al formulei corespunzătoare.

Pas interogare și activitate	Formulă
Sursă Conectarea la o sursă de date web	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Navigare Selectați tabelul de conectat	=Source{2}[Data]
Tip modificat Modificarea tipurilor de date (care Power Query face automat)	= 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}})
S-au eliminat alte coloane Eliminarea altor coloane pentru a afișa numai coloanele de interes	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Valoare înlocuită Înlocuirea valorilor pentru a curăța valorile dintr-o coloană selectată	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Rânduri filtrate Filtrarea valorilor dintr-o coloană	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Coloane redenumite S-au modificat anteturile de coloană pentru a fi semnificative	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Important    Aveți grijă la editarea pașilor Sursă, Navigare și Tip modificat,  deoarece acestea sunt create de Power Query pentru a defini și a configura sursa de date.

Afișarea sau ascunderea barei de formule

Bara de formule este afișată în mod implicit, dar dacă nu este vizibilă, o puteți reafișa.

• Selectați Vizualizare > Aspect > Bara de formule.

Edit a formula in the formula bar

1. Pentru a deschide o interogare, găsiți una încărcată anterior din Editor Power Query, selectați o celulă din date, apoi selectați Interogare > Editare. Pentru mai multe informații , consultați Crearea, încărcarea sau editarea unei interogări în Excel.

2. În panoul Setări interogare , sub Pași parcurși, selectați pasul pe care doriți să-l editați.

3. În bara de formule, găsiți și modificați valorile parametrilor, apoi selectați pictograma Enter sau apăsați pe Enter. De exemplu, modificați această formulă pentru a păstra și Coloana2:
   
   Înainte: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   După:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Selectați pictograma Enter sau apăsați pe Enter pentru a vedea noile rezultate afișate în Examinare date.

5. Pentru a vedea rezultatul într-o foaie de lucru Excel, selectați Pornire > Închidere & Încărcare.

Crearea unei formule în bara de formule

Pentru un exemplu de formulă simplă, să facem conversia unei valori text la corpul de literă potrivit, utilizând funcția Text.Proper.

1. Pentru a deschide o interogare necompletată, în Excel selectați Date > Obțineți > de datedin alte surse > Interogare necompletată. Pentru mai multe informații , consultați Crearea, încărcarea sau editarea unei interogări în Excel.

2. În bara de formule, introduceți=Text.Proper("text value"), apoi selectați pictograma Enter sau apăsați pe Enter.
   
   Rezultatele se afișează în Examinare date .

3. Pentru a vedea rezultatul într-o foaie de lucru Excel, selectați Pornire > Închidere & Încărcare.

Rezultat:

 Atunci când creați o formulă, Power Query validează sintaxa formulei. Totuși, atunci când inserați, reordonați sau ștergeți un pas intermediar dintr-o interogare, este posibil să întrerupeți o interogare.  Verificați întotdeauna rezultatele în Examinare date.

Important    Aveți grijă la editarea pașilor Sursă, Navigare și Tip modificat,  deoarece acestea sunt create de Power Query pentru a defini și a configura sursa de date.

Editarea unei formule utilizând o casetă de dialog

Această metodă utilizează casete de dialog care variază în funcție de pas. Nu trebuie să cunoașteți sintaxa formulei.

1. Pentru a deschide o interogare, găsiți una încărcată anterior din Editor Power Query, selectați o celulă din date, apoi selectați Interogare > Editare. Pentru mai multe informații , consultați Crearea, încărcarea sau editarea unei interogări în Excel.

2. În panoul Setări interogare , sub Pași parcurși, selectați pictograma Editare setări a pasului pe care doriți să-l editați sau faceți clic dreapta pe pas, apoi selectați Editare setări.

3. În caseta de dialog, efectuați modificările, apoi selectați OK.

Inserați un pas

După ce terminați un pas de interogare care vă remodelează datele, se adaugă un pas de interogare sub pasul de interogare curent. dar atunci când inserați un pas de interogare în mijlocul pașilor, poate apărea o eroare în pașii următori. Power Query afișează un avertisment Inserare pas atunci când încercați să inserați un pas nou și noul pas modifică câmpurile, cum ar fi numele coloanelor, care sunt utilizate în oricare dintre pașii care urmează pasului inserat.

1. În panoul Setări interogare , sub Pași parcurși, selectați pasul care doriți să precede imediat noul pas și formula corespunzătoare a acestuia.

2. Selectați pictograma Adăugare pas din partea stângă a barei de formule. Ca alternativă, faceți clic dreapta pe un pas, apoi selectați Inserare pas după. Se creează o formulă nouă în formatul :
   
   = <nameOfTheStepToReference>, cum ar fi =Production.WorkOrder.

3. Tastați noua formulă utilizând formatul:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   De exemplu, să presupunem că aveți un tabel cu coloana Gen și doriți să adăugați o coloană cu valoarea "Ms". sau "Mr.", în funcție de genul persoanei. Formula ar fi:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Reordonarea unui pas

• În panoul Setări interogări de sub Pași parcurși, faceți clic dreapta pe pas, apoi selectați Mutare în sus sau Mutare în jos.

Ștergere pas

• Selectați pictograma Ștergere din partea stângă a pasului sau faceți clic dreapta pe pas, apoi selectați Ștergere sau Ștergere până la sfârșit. Pictograma Ștergere este disponibilă și în partea stângă a barei de formule.

În acest exemplu, să facem conversia textului dintr-o coloană la corpul de literă potrivit, utilizând o combinație de formule din Editor avansat. 

De exemplu, aveți un tabel Excel, denumit Comenzi, cu o coloană NumeProdus căruia doriți să îi efectuați conversia la corpul de literă potrivit. 

Înainte:

După:

Atunci când creați o interogare complexă, creați o serie de pași de formule de interogare pe baza expresiei let. Utilizați expresia let pentru a atribui nume și a calcula valorile la care face referire apoi clauza in , care definește Pasul. Acest exemplu returnează același rezultat ca cel din secțiunea "Crearea unei formule în bara de formule".

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

Veți vedea că fiecare pas se bazează pe un pas anterior făcând referire la un pas cu nume. Ca memento, limbajul formulei Power Query este sensibil la litere mari și mici.

Faza 1: Deschideți Editor avansat

1. În Excel, selectați Date > Preluare date > Alte surse > Interogare necompletată. Pentru mai multe informații , consultați Crearea, încărcarea sau editarea unei interogări în Excel.

2. În Editor Power Query, selectați Pornire > Editor avansat, care se deschide cu un șablon al expresiei let.

Faza 2: Definiți sursa de date

1. Creați expresia let utilizând funcția Excel.CurrentWorkbook după cum urmează:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   #x4Source

2. Pentru a încărca interogarea într-o foaie de lucru, selectați Terminat, apoi selectați Pornire > Închidere & Încărcare > Închidere & Încărcare.

Rezultat:

Faza 3: Promovarea primului rând ca anteturi

1. Pentru a deschide interogarea, din foaia de lucru, selectați o celulă din date, apoi selectați Interogare > Editare. Pentru mai multe informații, consultați Crearea, încărcarea sau editarea unei interogări în Excel (Power Query).

2. În Editor Power Query, selectați Pornire > Editor avansat, care se deschide cu instrucțiunea pe care ați creat-o în Faza 2: Definiți sursa de date.

3. În expresia let, adăugați #"Primul rând ca antet" și Table.PromoteHeaders funcționează după cum urmează:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #x4#"First Row as Header" = Table.PromoteHeaders(Source)#x3
   

4. Pentru a încărca interogarea într-o foaie de lucru, selectați Terminat, apoi selectați Pornire > Închidere & Încărcare > Închidere & Încărcare.

Rezultat:

Faza 4: Modificați fiecare valoare dintr-o coloană la corpul de literă potrivit

1. Pentru a deschide interogarea, din foaia de lucru, selectați o celulă din date, apoi selectați Interogare > Editare. Pentru mai multe informații , consultați Crearea, încărcarea sau editarea unei interogări în Excel.

2. În Editor Power Query, selectați Pornire > Editor avansat, care se deschide cu instrucțiunea pe care ați creat-o în Faza 3: Promovați primul rând în anteturi.

3. În expresia let, efectuați conversia fiecărei valori de coloană NumeProdus la text corespunzător utilizând funcția Table.TransformColumns, făcând referire la pasul anterior al formulei de interogare "Primul rând ca antet", adăugând #"Fiecare Word cu majusculă" la sursa de date, apoi atribuind #"Fiecare Word cu majusculă" rezultatului 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"

4. Pentru a încărca interogarea într-o foaie de lucru, selectați Terminat, apoi selectați Pornire > Închidere & Încărcare > Închidere & Încărcare.

Rezultat:

Puteți controla comportamentul barei de formule din Editor Power Query pentru toate registrele de lucru.

Afișarea sau ascunderea barei de formule

1. Selectați Opțiuni> fișier și Setări > Opțiuni interogare.

2. În panoul din stânga, sub GLOBAL, selectați Editor Power Query.

3. În panoul din dreapta, sub Aspect, selectați sau debifați Afișare bară de formule.

Activarea sau dezactivarea M Intellisense

1. Selectați Opțiuni> fișier și Setări > Opțiuni interogare.

2. În panoul din stânga, sub GLOBAL, selectați Editor Power Query.

3. În panoul din dreapta, sub Formulă, selectați sau debifați Activare M Intellisense în bara de formule, editor complex și dialog coloană particularizată.

Notă    Modificarea acestei setări va avea efect data viitoare când deschideți fereastra Editor Power Query.