Crearea de formule Power Query în Excel

Editorul Power Query furnizează o experiență de interogare de date și modelare pentru Excel pe care o puteți utiliza pentru a remodela date din mai multe surse de date. Pentru a afișa fereastra Editor Power Query, importațidate din surse de date externe într-o foaie de lucru Excel, selectați o celulă din date, apoi selectați Interogare >Editare. În cele ce urmează se află un rezumat al componentelor principale.

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

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

3. Meniuri contextuale convenabile pentru comenzile din panglică

4. Previzualizarea datelor care afișează rezultatele pașilor aplicate la date

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

În culise, fiecare pas din 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ă Limbajul formulelor Power Query, pe care îl puteți utiliza pentru a construi atât expresii simple, cât și complexe. Pentru mai multe informații despre sintaxă, argumente, observații, funcții și exemple, consultați Limbajul de formule Power Query M.

În panoul Setări interogare, sub Pași aplicați,sunt creați pași de interogare și formule corespunzătoare pentru fiecare activitate. Mai jos este afișate unele date de interogare și un exemplu detaliat al relației dintre pașii și formulele din acea interogare.

Query step and task	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 (pe care Power Query le modifică 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 Anteturile de coloană modificate pentru a fi semnificative	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Important    Fiți atent la editarea pașilorpentru 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 reajuca.

• Selectați Vizualizare > aspect > de formule.

Edit a formula in the formula bar

1. Pentru a deschide o interogare, găsiți una încărcată anterior din Editorul 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 aplicați, selectațipasul 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 de 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ă efectuați conversia unei valori text la tipul potrivit, utilizând funcția Text.Proper.

1. Pentru a deschide o interogare necompletată, în Excel, selectați Date > Get Data > From Other Sources > Blank Query. 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 de meniu Enter sau apăsați pe Enter.
   
   Rezultatele se afișează în Previzualizare date.

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

Rezultat:

 Când creați o formulă, Power Query validează sintaxa formulei. Cu toate acestea, atunci când inserați, reordonarea sau ștergeți un pas intermediar într-o interogare, este posibil să întreruperea unei interogări.  Verificați întotdeauna rezultatele în Previzualizare date.

Important    Fiți atent la editarea pașilorpentru 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ă casetele de dialog care variază în funcție de pas. Nu este nevoie să știți sintaxa formulei.

1. Pentru a deschide o interogare, găsiți una încărcată anterior din Editorul 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 aplicaț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.

Inserarea unui pas

După ce terminați un pas de interogare care 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, iar noul pas modifică câmpurile, cum ar fi numele de coloane, care sunt utilizate în oricare dintre pașii care urmează pasului inserat.

1. În panoul Setări interogare, sub Pași aplicați,selectați pasul pe care doriți să-l precede imediat noul pas și formula sa corespunzătoare.

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 format :
   
   = <nameOfTheStepToReference>, cum ar fi =Production.WorkOrder.

3. Tastați formula nouă utilizând formatul:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   De exemplu, să presupunem că aveți un tabel cu coloana Sex și doriți să adăugați o coloană cu valoarea "Ms". sau "Dl.", în funcție de sexul 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 aplicaț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 Ștergeresau Ș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 literele mari și fără litere mari, utilizând o combinație de formule în Editorul complex. 

De exemplu, aveți un tabel Excel, denumit Comenzi, cu o coloană NumeProduis pe care doriți să o convertiți la tipul potrivit de situație. 

Înainte:

După:

Atunci când creați o interogare complexă, creați o serie de pași ai formulei de interogare pe baza expresiei permiteți.  Utilizați expresia let pentru a atribui nume și a calcula valorile la care se face referire apoi în 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 după nume. Drept memento, Limbajul formulelor Power Query este sensibil la litere mari și mici.

Faza 1: Deschideți Editorul complex

1. În Excel, selectați Data> Get Data > Other Sources > Blank Query. Pentru mai multe informații, consultați Crearea, încărcarea sau editarea unei interogări în Excel.

2. În Editorul Power Query, selectați Pornire> Editor complex, 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#x1
   
   in
       Source
   
   

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: Promovați primul rând la 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 Editorul Power Query, selectați Pornire >Editor complex, care se deschide cu inserția pe care ați creat-o în Faza 2: Definiți sursa de date.

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

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 din coloană la cazul 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 Editorul Power Query, selectați Pornire >Editor complex, care se deschide cu inserția pe care ați creat-o în Faza 3: Promovați primul rând la anteturi.

3. În expresia let, efectuați conversia fiecărei valori de coloană NumePro productName la un text corespunzător utilizând funcția Table.TransformColumns, făcând referire la pasul anterior de formulă de interogare "Primul rând ca anteturi", adăugând #"Capitalized Each Word" la sursa de date, apoi atribuind #"Capitalized Each Word" la rezultatul 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 Editorul Power Query pentru toate registrele dvs. de lucru.

Afișarea sau ascunderea barei de formule

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

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

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

Activarea sau dezactivarea M Intellisense

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

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

3. În panoul din dreapta, sub Formulă, bifați sau debifați Enable M Intellisense în barade formule, advanced editor și custom column dialog .

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