Ustvarjanje formul dodatka Power Query v Excelu

Če uporabljate urejevalnik Power Query, ste že ves čas ustvarjali formule dodatka Power Query. O poglejmo, kako Power Query deluje, tako, da si o ogledamo pokrov. Če želite izvedeti, kako posodobite ali dodate formule, si oglejte delovanje urejevalnika Power Query.  Z dodatnim urejevalnikom lahko celo poiskate svoje formule.           

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.

Deli urejevalnika poizvedb

  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.

Primer formule urejevalnika poizvedb

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.

Vaš brskalnik ne podpira videoposnetkov.

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

Results of the walkthrough - the first few rows

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 Ikona »Enter« na levi strani vnosne vrstice dodatka Power Query 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 Ikona »Enter« na levi strani vnosne vrstice dodatka Power Query 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 Ikona »Enter« na levi strani vnosne vrstice dodatka Power Query ali pritisnite tipko Enter.

    Rezultati so prikazani v predogledu podatkov.

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

Rezultat:

Text.Proper

 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 Ikona »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 Ikona funkcije 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.")

Primer formule

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 Izbriši korak 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 Izbriši korak 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:

Pred

Po:

4. korak – rezultat

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.

Dodatni urejevalnik 2

2. faza: Določanje vira podatkov

  1. Ustvarite izraz let z Excel. Funkcija CurrentWorkbook je na primer funkcija:

    let#x1

    in
        Source

    1. korak – dodatni urejevalnik

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

Rezultat:

1. korak – 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:

3. korak – 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:

4. korak – 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.

Glejte tudi

Pomoč za Power Query Excel za windows

Ustvarjanje in priklic funkcije po meri

Uporaba seznama Uporabljeni koraki (docs.com)

Uporaba funkcij po meri (docs.com)

Formule Power Query M (docs.com)

Ravnanje z napakami (docs.com)

Ali potrebujete dodatno pomoč?

Razširite poznavanje Officea
Oglejte si izobraževanje
Prvi dobite nove funkcije
Pridružite se programu Office Insider

Vam je bila informacija v pomoč?

Zahvaljujemo se vam za povratne informacije. Videti je, da bi vam prišla prav pomoč enega od naših Officeovih agentov za podporo.

×