Ustvarjanje formul dodatka Power Query v Excelu

V urejevalnik Power Query je na voljo poizvedba za podatke in izkušnja oblikovanja za Excel, s katero lahko preobliknete podatke iz številnih virov podatkov. Če želite urejevalnik Power Query okno za vnospodatkov, uvozite podatke iz zunanjih virov podatkov na Excelov delovni list, izberite celico v podatkih in nato izberite Poizvedba >Uredi. V nadaljevanju je povzetek glavnih komponent.

1. The urejevalnik Power Query ribbon that you use to shape your data

2. Podokno s poizvedbami, v katerem poiščete vire podatkov in tabele

3. Priročni meniji, ki so priročni bližnjici do ukazov na traku

4. The Data Preview that displays the results of the steps applied to the data

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

V ozadju vsak korak v poizvedbi temelji na formuli, ki je vidna v vnosni vrstici.

Včasih boste morda želeli spremeniti ali ustvariti formulo. Formule uporabljajo jezik Power Query formule, s katerim lahko ustvarite tako preproste kot tudi zapletene izraze. Če želite več informacij o sintaksi, argumentih, pripombah, funkcijah in primerih, glejte Power Query jeziku formul M.

Če za primer uporabite seznam nogometnega prvenstva, uporabite Power Query, da prenesete neobdelane podatke, ki ste jih našli na spletnem mestu, in jih spremenite v dobro oblikovano tabelo. Oglejte si, kako so koraki poizvedbe in ustrezne formule ustvarjeni za vsako opravilo v podoknu Nastavitve poizvedbe v razdelku Uporabljeni koraki in v vnosni vrstici.

Postopek

1. Če želite uvoziti podatke, izberite > iz spleta, v polje URL vnesite »http://en.wikipedia.org/wiki/UEFA_European_Football_Championship« in nato izberite V redu.

2. V pogovornem oknu Krmar izberite tabelo Rezultati [Uredi] na levi strani in nato izberite Pretvori podatke na dnu. Prikaže Power Query urejevalnik.

3. Če želite spremeniti privzeto ime poizvedbe, v podoknu Nastavitve poizvedbe v razdelku Lastnosti izbrišite »Rezultati [Uredi]« in vnesite »Šampioni UEFA«.

4. Če želite odstraniti neželene stolpce, izberite prvi, četrti in peti stolpec, nato pa izberite Osnovno> Odstrani stolpce > Odstrani druge stolpce.

5. Če želite odstraniti neželene vrednosti, izberite Stolpec1, izberite Osnovno > Zamenjaj vrednosti, v polje Vrednosti za iskanje vnesite »podrobnosti« in nato izberite V redu.

6. Če želite odstraniti vrstice z besedo »Year« v njih, izberite puščico filtra v stolpcu1, počistite potrditveno polje ob možnosti »Leto« in izberite V redu.

7. Če želite preimenovati glave stolpcev, dvokliknite vsako od njih in nato spremenite »Stolpec1« v »Leto«, »Stolpec4« v »Zmagovalec« in »Stolpec5« v »Končni rezultat«.

8. Če želite shraniti poizvedbo, izberite Osnovno > Zapri & Naloži.

Rezultat

V spodnji tabeli je povzetek vsakega uporabljenega koraka in ustrezne formule.

Korak poizvedbe in opravilo	Formula
Vir Povezovanje s spletnim virom podatkov	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Krmarjenje Izbiranje tabele	=Source{2}[Data]
Spremenjena vrsta Spreminjanje podatkovnih tipov (ki Power Query samodejno)	= 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}})
Odstranjeni drugi stolpci Odstranjevanje drugih stolpcev in prikaz le želenih stolpcev	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Zamenjana vrednost Zamenjava vrednosti za čiščenje vrednosti v izbranem stolpcu	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Filtrirane vrstice filtriranje vrednosti v stolpcu	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Preimenovani stolpci Spremenjene glave stolpcev, da bodo smiselne	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Pomembno    Pazite na urejanje korakov Vir, Krmarjenje  in Spremenjena vrsta, ker jih ustvari Power Query določi in nastavi vir podatkov.

Prikazovanje ali skrivanje vnosne vrstice

Vnosna vrstica je privzeto prikazana, če pa ni vidna, jo lahko znova prikažete.

• Izberite Pogled > postavitev > vnosno vrstico.

Dodajanjeformule v vnosno vrstico

1. Če želite odpreti poizvedbo, poiščite poizvedbo, ki ste jo že naložili iz urejevalnik Power Query, izberite celico v podatkih in nato izberite Poizvedba > Uredi. Če želite več informacij , glejte Ustvarjanje, nalaganje ali urejanje poizvedbe v Excelu.

2. V podoknu Nastavitve poizvedbe v razdelku Uporabljeni koraki izberite korak, ki ga želite urediti.

3. V vnosni vrstici poiščite in spremenite vrednosti parametra, nato pa izberite ikono ali pritisnite tipko Enter. Spremenite na primer to formulo tako, da ohranite tudi Stolpec2:
   
   Pred: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   za:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Izberite ikono ali pritisnite Enter, da prikažete nove rezultate v predogledu podatkov.

5. Če si želite ogledati rezultat na Excelovem delovnem listu , izberite Osnovno > Zapri & naloži.

Ustvarjanje formule v vnosni vrstici

Za primer preproste formule pretvorimo besedilno vrednost v velike in male črke s funkcijo Text.Proper.

1. Če želite odpreti prazno poizvedbo, v Excelu izberite Možnost > pridobi podatke > iz drugih virov in > poizvedbo. Če želite več informacij , glejte Ustvarjanje, nalaganje ali urejanje poizvedbe v Excelu.

2. V vnosno vrstico vnesite=Text.Proper("text value")in nato izberite ikono vnos ali pritisnite tipko Enter.
   
   Rezultati so prikazani v predogledu podatkov.

3. Če si želite ogledati rezultat na Excelovem delovnem listu , izberite Osnovno > Zapri & naloži.

Rezultat:

 Ko ustvarite formulo, Power Query sintakso formule. Ko pa vstavite, preuredite ali izbrišete vmesni korak v poizvedbi, lahko poizvedbo morda prelomite.  Vedno preverite rezultate v predogledu podatkov.

Pomembno    Pazite na urejanje korakov Vir, Krmarjenje  in Spremenjena vrsta, ker jih ustvari Power Query določi in nastavi vir podatkov.

Urejanje formule v pogovornem oknu

Ta način uporablja pogovorna okna, ki se razlikujejo glede na korak. Ni vam treba poznati sintakse formule.

1. Če želite odpreti poizvedbo, poiščite poizvedbo, ki ste jo že naložili iz urejevalnik Power Query, izberite celico v podatkih in nato izberite Poizvedba > Uredi. Če želite več informacij , glejte Ustvarjanje, nalaganje ali urejanje poizvedbe v Excelu.

2. V podoknu Nastavitve poizvedbe v razdelku Uporabljeni koraki izberite ikono Uredi nastavitve koraka, ki ga želite urediti, ali pa ga kliknite z desno tipko miške, nato pa izberite Uredi nastavitve.

3. V pogovornem oknu vnesite spremembe in nato izberite V redu.

Vstavljanje koraka

Ko dokončate korak poizvedbe, ki preoblikuje vaše podatke, je korak poizvedbe dodan pod trenutni korak poizvedbe. ko pa vstavite korak poizvedbe na sredino korakov, lahko v naslednjih korakih pride do napake. Power Query prikaže opozorilo Vstavi korak, ko poskušate vstaviti nov korak, nov korak pa spremeni polja, kot so imena stolpcev, ki so uporabljena v katerem koli koraku, ki sledi vstavljenem koraku.

1. V podoknu Nastavitve poizvedbe v razdelku Uporabljeni koraki izberite korak, ki ga želite takoj pred novim korakom in njegovo ustrezno formulo.

2. Izberite ikono Dodaj na levi strani vnosne vrstice. Lahko pa tudi z desno tipko miške kliknete korak in nato izberete Vstavi korak za. Nova formula je ustvarjena v obliki zapisa := <nameOfTheStepToReference>
   
   , na primer =Production.WorkOrder.

3. Vnesite novo formulo v obliki zapisa:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Recimo, da imate tabelo s stolpcem »Spol« in želite dodati stolpec z vrednostjo »Ms«. ali "g.", odvisno od spola osebe. Formula bi bila:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Preurejanje koraka

• V podoknu Nastavitve poizvedb vrazdelku Uporabljeni koraki z desno tipko miške kliknite korak in nato izberite Premakni navzgor ali Premakni navzdol.

Izbriši korak

• Izberite ikono izbriši na levi strani koraka ali pa z desno tipko miške kliknite korak in nato izberite Izbriši ali Izbriši do konca. Ikona Izbriši je na voljo tudi levo od vnosne vrstice.

V tem primeru pretvorimo besedilo v stolpcu v velike in male črke s kombinacijo formul v napredni urejevalnik. 

Imate na primer Excelovo tabelo, imenovano »Naročila«, s stolpcem »ProductName«, ki ga želite pretvoriti v velike in male črke. 

Pred:

Po:

Ko ustvarite napredno poizvedbo, ustvarite niz korakov formule poizvedbe, ki temeljijo na izrazu let. Z izrazom LET dodelite imena in izračunajte vrednosti, na katere se nato sklicuje stavek In , ki določa korak. V tem primeru je vrnjen isti rezultat kot rezultat v razdelku »Ustvarjanje formule v vnosni vrstici«.

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

Vsak korak se gradi na prejšnjem koraku tako, da se sklicujete na korak po imenu. Naj vas opomnimo, da Power Query jezik formule razlikuje med velikimi in malimi črkami.

1. faza: odprite napredni urejevalnik

1. V Excelu izberite Možnost> pridobi podatke >Druge vire in > prazno poizvedbo. Če želite več informacij , glejte Ustvarjanje, nalaganje ali urejanje poizvedbe v Excelu.

2. V urejevalnik Power Query izberite Osnovno > napredni urejevalnik, ki se odpre s predlogo izraza let.

2. faza: Določanje vira podatkov

1. Izraz Let lahko s funkcijo Excel.CurrentWorkbook ustvarite tako:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]#x2
   
       
   
   Source#x4

2. Če želite poizvedbo naložiti v delovni list, izberite Dokončano in natoizberite Osnovno > Zapri & > Zapri & Naloži.

Rezultat:

3. faza: povišanje prve vrstice v glave

1. Če želite odpreti poizvedbo, na delovnem listu izberite celico v podatkih in nato izberite Poizvedba > Uredi. Če želite več informacij, glejte Ustvarjanje, nalaganje ali urejanje poizvedbe v Excelu (Power Query).

2. V oknu urejevalnik Power Query izberite Osnovno> napredni urejevalnik, ki se odpre z izjavo, ki ste jo ustvarili v 2. fazi: Določite vir podatkov.

3. V izrazu let dodajte #"First Row as Header" in Table.PromoteHeaders tako:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
   #"First Row as Header" = Table.PromoteHeaders(Source)#x3
       #"First Row as Header"

4. Če želite poizvedbo naložiti v delovni list, izberite Dokončano in natoizberite Osnovno > Zapri & > Zapri & Naloži.

Rezultat:

4. faza: spreminjanje posamezne vrednosti v stolpcu v velike in male črke

1. Če želite odpreti poizvedbo, na delovnem listu izberite celico v podatkih in nato izberite Poizvedba > Uredi. Če želite več informacij , glejte Ustvarjanje, nalaganje ali urejanje poizvedbe v Excelu.

2. V urejevalnik Power Query izberite Zavihek Osnovno> napredni urejevalnik, ki se odpre z izjavo, ki ste jo ustvarili v 3. fazi: Povišanje prve vrstice v glave.

3. V izrazu let pretvorite vsako vrednost stolpca ProductName v ustrezno besedilo s funkcijo Table.TransformColumns, ki se sklicuje na prejšnji korak formule poizvedbe »Prva vrstica kot glava«, viru podatkov dodajte #"Capitalized Each Word" in nato v rezultat in dodelite #"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. Če želite poizvedbo naložiti v delovni list, izberite Dokončano in natoizberite Osnovno > Zapri & > Zapri & Naloži.

Rezultat:

Nadzorujete lahko vedenje vnosne vrstice v urejevalnik Power Query za vse delovne zvezke.

Prikaz ali skrivanje vnosne vrstice

1. Izberite Možnosti > možnosti in Nastavitve, >možnosti poizvedbe.

2. V levem podoknu v razdelku GLOBAL izberite urejevalnik Power Query.

3. V desnem podoknu v razdelku Postavitev potrdite ali počistite polje Prikaži vnosno vrstico.

Vklop ali izklop funkcije M Intellisense

1. Izberite Možnosti > in Nastavitve in >možnosti poizvedbe.

2. V levem podoknu v razdelku GLOBAL izberite urejevalnik Power Query.

3. V desnem podoknu v razdelku Formula potrdite ali počistite polje Omogoči M Intellisense v vnosni vrstici, dodatnem urejevalniku in pogovornem oknu stolpca po meri.

Opomba    Sprememba te nastavitve bo veljati, ko naslednjič odprete urejevalnik Power Query okno.