Duomenų importavimas iš aplanko su keliais failais ("Power Query")

Naudokite "Power Query" norėdami sujungti kelis failus su ta pačia schema, saugoma viename aplanke, į vieną lentelę. Pavyzdžiui, kiekvieną mėnesį norite sujungti kelių skyrių biudžeto darbaknyges, kuriose stulpeliai yra tokie patys, bet eilučių ir reikšmių skaičius kiekvienoje darbaknygėje skiriasi. Kai jį nustatysite, galėsite taikyti papildomas transformacijas, kaip tai darytumėte su vienu importuotu duomenų šaltiniu, tada atnaujinti duomenis , kad pamatytumėte kiekvieno mėnesio rezultatus.

Abstraktus aplankų failų sujungimo apžvalga

Pastaba Šioje temoje rodoma, kaip sujungti failus iš aplanko. Taip pat galite sujungti failus, saugomus "SharePoint", "Azure" didelių dvejetainių objektų saugykloje ir Azure Data Lake Storage. Procesas yra panašus.

Naujesnės versijos Office 2013

Išlaikykite tai paprasta:

Įsitikinkite, kad visi failai, kuriuos norite sujungti, yra skirtame aplanke be pašalinių failų. Kitu atveju visi aplanke esantys failai ir visi jūsų pasirinkti poaplankiai įtraukiami į jungiamus duomenis.
Kiekvienas failas turi turėti tą pačią schemą su nuosekliomis stulpelių antraštėmis, duomenų tipais ir stulpelių skaičiumi. Stulpeliai turi būti ne ta pačia tvarka, kokia sutampa stulpelių pavadinimai.
Jei įmanoma, venkite nesusijusių duomenų šaltinių, kuriuose gali būti daugiau nei vienas duomenų objektas, pvz., JSON failo, "Excel" darbaknygės arba "Access" duomenų bazės.

Kiekvienas iš šių failų atitinka paprastą modelį, kiekviename faile yra tik viena duomenų lentelė.

Pasirinkite Duomenų > Gauti duomenis > iš failo > iš aplanko. Rodomas dialogo langas Naršyti .
Raskite aplanką, kuriame yra failai, kuriuos norite sujungti.
Aplanke esančių failų sąrašas rodomas dialogo lange <aplanko kelias>. Patikrinkite, ar sąraše yra visi norimi failai.
Pasirinkite vieną iš dialogo lango apačioje rodomų komandų, pvz., Derinti > Derinti & Įkelti. Skyriuje Apie visas šias komandas aptariamos papildomos komandos.
Jei pasirinksite bet kurią komandą Jungti , bus rodomas dialogo langas Failų jungimas . Norėdami pakeisti failo parametrus, pasirinkite kiekvieną failą lauke Failo pavyzdys , nustatykite failo kilmę, skyriklį ir duomenų tipo aptikimą , kaip norite. Taip pat galite pažymėti arba išvalyti žymės langelį Praleisti failus su klaidomis dialogo lango apačioje.
Pažymėkite Gerai.

Rezultatas

"Power Query" automatiškai sukuria užklausas, kad sutrauktų kiekvieno failo duomenis į darbalapį. Užklausos veiksmai ir stulpeliai, sukurti, priklauso nuo to, kurią komandą pasirinksite. Daugiau informacijos žr. skyriuje Apie visas šias užklausas.

Pasirinkite Duomenų > Gauti duomenis > iš failo > iš aplanko. Rodomas dialogo langas Naršyti.
Raskite aplanką, kuriame yra failai, kuriuos norite sujungti.
Aplanke esančių failų sąrašas rodomas dialogo lange <aplanko kelias>. Patikrinkite, ar sąraše yra visi norimi failai.
Pasirinkite vieną iš dialogo lango apačioje pateikiamų komandų, pvz., Derinti > Derinti & Transformuoti. Skyriuje Apie visas šias komandas aptariamos papildomos komandos.

Rodomas "Power Query" rengyklė.
Stulpelis Reikšmė yra struktūrinis stulpelis Sąrašas . Pasirinkite piktogramą Išplėsti , tada pasirinkite Išplėsti iki naujų eilučių.
Stulpelis Reikšmė dabar yra struktūruotas stulpelis Įrašas . Pasirinkite piktogramą Išplėsti . Rodomas išplečiamasis dialogo langas.
Išsaugoti visus pažymėtus stulpelius. Galite išvalyti žymės langelį Naudoti pradinį stulpelio pavadinimą kaip prefiksą . Pažymėkite Gerai.
Pažymėkite visus stulpelius, kuriuose yra duomenų reikšmių. Pasirinkite Pagrindinis, rodyklę šalia Pašalinti stulpelius, tada pasirinkite Šalinti kitus stulpelius.
Pasirinkite Pagrindinis > Uždaryti & Įkelti.

Rezultatas

Kiekvienas iš šių duomenų šaltinių gali turėti daugiau nei vieną importuotinų objektų. "Excel" darbaknygėje gali būti keli darbalapiai, "Excel" lentelės arba pavadinti diapazonai. "Access" duomenų bazėje gali būti kelios lentelės ir užklausos.

Pasirinkite Duomenų > Gauti duomenis > iš failo > iš aplanko. Rodomas dialogo langas Naršyti.
Raskite aplanką, kuriame yra failai, kuriuos norite sujungti.
Aplanke esančių failų sąrašas rodomas dialogo lange <aplanko kelias>. Patikrinkite, ar sąraše yra visi norimi failai.
Pasirinkite vieną iš dialogo lango apačioje rodomų komandų, pvz., Derinti > Derinti & Įkelti. Skyriuje Apie visas šias komandas aptariamos papildomos komandos.
Dialogo lange Failų jungimas :
- Lauke Failo pavyzdys pasirinkite failą, kurį naudosite kaip duomenų pavyzdį, naudojamą užklausoms kurti. Negalite pasirinkti objekto arba pasirinkti tik vieną objektą. Tačiau negalite pasirinkti daugiau nei vieną.
- Jei turite daug objektų, naudokite ieškos lauką , kad rastumėte objektą, arba Rodymo parinktys kartu su mygtuku Atnaujinti , kad filtruotumėte sąrašą.
- Pažymėkite arba išvalykite žymės langelį Praleisti failus su klaidomis dialogo lango apačioje.
Pažymėkite Gerai.

Rezultatas

"Power Query" automatiškai sukuria užklausą, kad sutrauktų kiekvieno failo duomenis į darbalapį. Užklausos veiksmai ir stulpeliai, sukurti, priklauso nuo to, kurią komandą pasirinksite. Daugiau informacijos žr. skyriuje Apie visas šias užklausas.

Norėdami užtikrinti didesnį lankstumą, galite aiškiai sujungti failus "Power Query" rengyklė naudodami komandą Sujungti failus. Tarkime, kad šaltinio aplanke yra failų tipų ir poaplankių, o jūs norite taikyti tam tikrus failus su tuo pačiu failų tipu ir schema, bet ne kitus. Tai gali pagerinti našumą ir supaprastinti transformacijas.

Pasirinkite Duomenų > Gauti duomenis > iš failo > iš aplanko. Rodomas dialogo langas Naršyti .
Raskite aplanką, kuriame yra norimos sujungti failai, tada pasirinkite Atidaryti.
Dialogo lange <aplanko kelias> rodomas visų failų, esančių aplanke ir poaplankiuose, sąrašas. Patikrinkite, ar sąraše yra visi norimi failai.
Apačioje pasirinkite Transformuoti duomenis . Atidaromas "Power Query" rengyklė ir rodomi visi aplanke ir visuose poaplankiuose esantys failai.
Norėdami pasirinkti norimus failus, filtruokite stulpelius, pvz., Plėtinys arba Aplanko kelias.
Norėdami sujungti failus į vieną lentelę, pasirinkite stulpelį Turinys , kuriame yra kiekvienas dvejetainis (paprastai pirmas stulpelis), tada pasirinkite Pagrindinis > Jungti failus. Rodomas dialogo langas Failų jungimas .
"Power Query" analizuoja failo pavyzdį pagal numatytuosius nustatymus pirmąjį sąrašo failą, kad naudotų tinkamą jungtį ir identifikuotų atitinkančius stulpelius.

Norėdami naudoti kitą failo pavyzdį, pasirinkite jį išplečiamajame sąraše Failo pavyzdys .
Jei norite, apačioje pasirinkite Praleisti failus su klaidomis, kad šie failai nebūtų įtraukti į rezultatą.
Pažymėkite Gerai.

Rezultatas

"Power Query" automatiškai sukuria užklausas, kad kiekvieno failo duomenys būtų sutraukti į darbalapį. Užklausos veiksmai ir stulpeliai, sukurti, priklauso nuo to, kurią komandą pasirinksite. Daugiau informacijos žr. skyriuje Apie visas šias užklausas.

Galite pasirinkti kelias komandas ir kiekviena jų turi skirtingą paskirtį.

Duomenų sujungimas ir transformavimas Norėdami sujungti visus failus su užklausa, tada paleisti "Power Query" rengyklė, pasirinkite Jungti > sujungti ir transformuoti duomenis.
Jungimas ir įkėlimas Norėdami rodyti dialogo langą Failo pavyzdys , sukurkite užklausą ir įkelkite ją į darbalapį, pasirinkite Derinti > Derinti ir įkelti.
Derinti ir įkelti į Norėdami, kad būtų rodomas dialogo langas Failo pavyzdys , sukurkite užklausą, tada rodydami dialogo langą Importuoti pasirinkite Derinti > Derinti ir Įkelti į.
Apkrovos Norėdami sukurti užklausą vienu veiksmu, tada įkelti į darbalapį, pasirinkite Įkelti > Įkelti.
Įkelti į Norėdami sukurti užklausą vienu veiksmu, tada atidaryti dialogo langą Importuoti , pasirinkite Įkelti > Įkelti į.
Transformuoti duomenisNorėdami sukurti užklausą vienu veiksmu, tada paleisti "Power Query" rengyklė, pasirinkite Transformuoti duomenis.

Tačiau sujungiate failus, kelios palaikymo užklausos sukuriamos srityje Užklausos , esančioje grupėje "Pagalbinės užklausos".

Užklausų, sukurtų srityje Užklausos, sąrašas

"Power Query" sukuria užklausą "Failo pavyzdys" pagal užklausos pavyzdį.
Funkcijos Transformuoti failą užklausa naudoja užklausą "Parameter1", kad nurodytų kiekvieną failą (arba dvejetainį) kaip įvestį į užklausą "Pavyzdžio failas". Ši užklausa taip pat sukuria stulpelį Turinys , kuriame yra failo turinys, ir automatiškai išplečia struktūrinį stulpelį Įrašas , kad į rezultatus įtrauktų stulpelio duomenis. Užklausos "Transformuoti failą" ir "Failo pavyzdys" yra susietos, kad užklausos "Failo pavyzdys" pakeitimai atsispindėtų užklausoje "Transformuoti failą".
Užklausa, kurioje yra galutiniai rezultatai, yra grupėje "Kitos užklausos". Pagal numatytuosius parametrus jis pavadintas po aplanku, iš kurio importavote failus.

Norėdami atlikti tolesnį tyrimą, dešiniuoju pelės mygtuku spustelėkite kiekvieną užklausą ir pasirinkite Redaguoti , kad ištirtumėte kiekvieną užklausos veiksmą ir pamatytumėte, kaip užklausos veikia kartu.

Norėdami pradėti kelių failų sujungimo procesą, pirmiausia įsitikinkite, kad visi failai, kuriuos norite sujungti, yra viename failų aplanke.

Pastaba: Palaikomi "Excel", teksto, CSV, JSON, XML ir "Access" failai.

Spustelėkite skirtuką "Power Query", tada Iš failo > iš aplanko.
Spustelėkite mygtuką Naršyti , kad rastumėte savo aplanką.

Bus rodomas aplanke esančių failų sąrašas. Pasirinkite parinktį Redaguoti, kad atidarytumėte Užklausų rengyklė. Šiuo atveju įkeliame 4 "Excel" darbaknyges.
Patvirtinę, kad visi norimi failai yra išvardyti, spustelėkite mygtuką Derinti dvejetainius , esantį stulpelyje Turinys .
Kiekvienas failas analizuojamas ir nustatomas tinkamas failo formatas, pvz., tekstas, "Excel" arba JSON. Tokiu atveju rodomas pirmos "Excel" darbaknygės darbalapių sąrašas. Pasirinkite norimą naudoti darbalapį, tada spustelėkite Gerai.
Sujungus dvejetainių duomenų transformavimo procesą, bus automatiškai sukurta užklausa, kuri sutrauks kiekvieno failo duomenis ir rodys peržiūrą. Jei rezultatas jus tenkina, spustelėkite Uždaryti & Įkelti.

Sujungus dvejetainius failus, išvardyti darbalapiai bus sutraukti į vieną darbalapį.
Pasikeitus pradiniams duomenų failams, visada galite atnaujinti importuotus duomenis. Spustelėkite bet kurioje duomenų diapazono vietoje, tada eikite į Užklausos įrankiai > Atnaujinti. Be to, galite lengvai pritaikyti papildomus transformavimo arba išskleidimo veiksmus modifikuodami automatiškai sukurtą exemplar užklausą, nesirūpindami dėl papildomų funkcijų užklausų veiksmų modifikavimo ar kūrimo; visi exemplar užklausos pakeitimai automatiškai sugeneruojami susietos funkcijos užklausoje.

Žinomos problemos

Įkeliant dvejetainių duomenų jungimo procedūrą į "Excel", gali būti rodomas šis klaidos pranešimas:

Derinti dvejetainius klaidos pranešimą. Tai žinoma klaida, į kurią sprendžiama.

Jei atmesite klaidą (-es) spustelėdami Gerai, nepaisant klaidos , dvejetainių duomenų jungimo procesas bus baigtas ir tinkamai įkeltas. Stengiamės ištaisyti klaidą.