Ustvarjanje formul dodatka Power Query v Excelu

Urejevalnik dodatka Power Query omogoča izkušnjo poizvedbe podatkov in oblikovanja za Excel, ki jih lahko uporabite za preoblikovanje podatkov iz številnih virov podatkov. Če želite prikazati okno Urejevalnik dodatka Power Query,uvozite podatke iz zunanjih virov podatkov na delovnem listu programa Excel, izberite celico v podatkih in nato izberite Poizvedba >Uredi. V nadaljevanju je povzetek glavnih komponent.

1. Trak urejevalnika za Power Query, ki ga uporabljate za oblikovanje podatkov

2. Podokno »Poizvedbe« za iskanje virov podatkov in tabel

3. Priročni meniji, ki so priročni za ukaze na traku

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

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

V zakulisju 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 formul Power Query, s katerim lahko ustvarite tako preproste kot tudi zapletene izraze. Če želite več informacij o sintaksi, argumentih, pripombah, funkcijah in primerih, glejte Jezik formul M za Power Query.

Če kot primer uporabite seznam nogometnega tekmovanja, uporabite Power Query in vzemite neobdelane podatke, ki ste jih našli na spletnem mestu, ter jih nato preoblikovati v dobro oblikovano tabelo. Oglejte si, kako so koraki poizvedbe in ustrezne formule ustvarjeni za vsako opravilo v podoknu poizvedbe Nastavitverazdelku 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, nato pa izberite Pretvori podatke na dnu. Prikaže se urejevalnik Power Query.

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

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

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

6. Če želite odstraniti vrstice z besedo »Leto« 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 vsakega od njih in nato spremenite »Stolpec1« v »Leto«, »Stolpec4« v »Zmagovalec« in »Stolpec5« v »Končna ocena«.

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

Rezultat

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

Korak in opravilo poizvedbe	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 (kar Power Query naredi 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 so smiselne	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Pomembno    Pazite pri urejanju korakov Vir, Krmarjenje in Spremenjena vrsta, saj jih ustvari Power Query, da določi   in nastavi vir podatkov.

Prikaz ali skrivanje vnosne vrstice

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

• Izberite Pogled > postavitve >vnosno vrstico.

Vnosformule v vnosno vrstico

1. Če želite odpreti poizvedbo, jo poiščite v urejevalniku dodatka Power Query, izberite celico v podatkih in nato izberite Poizvedba> Uredi. Če želite več informacij, glejte Ustvarjanje, nalaganje ali urejanje poizvedbe v Excel.

2. V podoknu Nastavitve v razdelku Uporabljeni korakiizberite korak, ki ga želite urediti.

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

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

5. Če si želite ogledate rezultat Excel delovnem listu, izberite Osnovno> Zapri & nalaganje.

Ustvarjanje formule v vnosni vrstici

Za primer preproste formule pretvorimo besedilno vrednost v ustrezne črke z uporabo funkcije Text.Proper.

1. Če želite odpreti prazno poizvedbo, Excel Data> Get Data > From Other Sources > Blank Query(Dobi podatke iz drugih virov) . Če želite več informacij, glejte Ustvarjanje, nalaganje ali urejanje poizvedbe v Excel.

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

3. Če si želite ogledate rezultat Excel delovnem listu, izberite Osnovno> Zapri & nalaganje.

Rezultat:

 Ko ustvarite formulo, Power Query preveri veljavnost sintakse formule. Ko pa vstavite, preuredite ali izbrišete vmesni korak v poizvedbi, lahko poizvedbo prekršite.  Vedno preverite rezultate v predogledu podatkov.

Pomembno    Pazite pri urejanju korakov Vir, Krmarjenje in Spremenjena vrsta, saj jih ustvari Power Query, da določi   in nastavi vir podatkov.

Urejanje formule v pogovornem oknu

Na ta način uporabite pogovorna okna, ki se razlikujejo glede na korak. Ni vam treba poznati sintakse formule.

1. Če želite odpreti poizvedbo, jo poiščite v urejevalniku dodatka Power Query, izberite celico v podatkih in nato izberite Poizvedba> Uredi. Če želite več informacij, glejte Ustvarjanje, nalaganje ali urejanje poizvedbe v Excel.

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

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

Vstavljanje koraka

Ko dokončate korak poizvedbe, ki preoblikova podatke, je pod trenutni korak poizvedbe dodan korak poizvedbe. toda ko 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 v razdelku Uporabljenikoraki izberite korak, ki ga želite takoj pred novim korakom in njegovo ustrezno formulo.

2. Izberite ikono dodaj korak 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. Novo formulo vnesite 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 razdelku Uporabljeni korakiz desno tipko miške kliknite korak in nato izberite Premakni navzgor ali Premakni navzdol.

Izbriši korak

• Izberite ikono vrstice na levi strani koraka ali z desno tipko miške kliknite korak, nato pa izberite Izbriši ali Izbriši, dokler se ne konča. Ikona izbriši je na voljo tudi na levi strani vnosne vrstice.

V tem primeru pretvorimo besedilo v stolpcu v ustrezne črke s kombinacijo formul v dodatnem urejevalniku. 

Imate lahko na primer tabelo Excel z imenom »Naročila« s stolpcem »ProductName«, ki ga želite pretvoriti v zapis z veliko in veliko širino. 

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 rezultat enak rezultatu v razdelku »Ustvarjanje formule v vnosni vrstici«.

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

Vsak korak temelji na prejšnjem koraku tako, da se sklicuje na korak z imenom. Kot opomnik, Jezik formul za Power Query med malimi in velikimi črkami.

1. faza: odpiranje dodatnega urejevalnika

1. V Excel izberite Data> Get Data > Other Sources > Blank Query (Dobi podatke iz > poizvedbo). Če želite več informacij, glejte Ustvarjanje, nalaganje ali urejanje poizvedbe v Excel.

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

2. faza: Določanje vira podatkov

1. Ustvarite izraz let z Excel. Funkcija CurrentWorkbook je na primer funkcija:
   
   let#x1
   
   in
       Source
   
   

2. Če želite poizvedbo naložiti v delovni list, izberite Končano ,nato pa izberite Osnovno> Zapri & Naloži > Zapri & naloži.

Rezultat:

3. faza: povišate prvo vrstico v glave

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

2. V urejevalniku dodatka Power Query izberite Osnovno> Dodatni 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 Končano ,nato pa izberite Osnovno> Zapri & Naloži > Zapri & naloži.

Rezultat:

4. faza: Spreminjanje posamezne vrednosti v stolpcu v ustrezno

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

2. V urejevalniku dodatka Power Query izberite Osnovno > Dodatni urejevalnik, ki se odpre z izjavo, ki ste jo ustvarili v 3. fazi:prvo vrstico povišate 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 »First Row as Header«, viru podatkov dodajte #"Capitalized Each Word" in nato dodelite #"Capitalized Each Word" v rezultat.
   
   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 Končano ,nato pa izberite Osnovno> Zapri & Naloži > Zapri & naloži.

Rezultat:

Nadzirate lahko vedenje vnosne vrstice v urejevalniku dodatka Power Query za vse delovne zvezke.

Prikaz ali skrivanje vnosne vrstice

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

2. V levem podoknu v razdelku GLOBAL izberiteUrejevalnik za Power Query.

3. V desnem podoknu v razdelku Postavitevizberite ali počistite možnost Prikaži vnosno vrstico.

Vklop ali izklop storitve M Intellisense

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

2. V levem podoknu v razdelku GLOBAL izberiteUrejevalnik za Power Query.

3. V desnem podoknu v razdelku Formulaizberite ali počistite potrditveno 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 okno urejevalnika power query.