„Power Query“ yra naujas „Microsoft“ įrankis duomenims išgauti, transformuoti ir įkelti. Šiandienos straipsnis yra apie visų aplanke esančių failų apdorojimą.
„Power Query“ yra integruota į „Excel 2016“ ir ją galima nemokamai atsisiųsti iš tam tikrų „Excel 2010“ ir „Excel 2013“ versijų. Šis įrankis skirtas išgauti, transformuoti ir įkelti duomenis į „Excel“ iš įvairių šaltinių. Geriausia dalis: „Power Query“ prisimena jūsų veiksmus ir atkurs juos, kai norėsite atnaujinti duomenis. Kai ši knyga bus spausdinama, „Excel 2016“ „Power Query“ funkcijos yra skirtuke Duomenys, grupės „Gauti ir transformuoti“ skiltyje „Nauja užklausa“. Sunku nuspėti, ar „Microsoft“ atgaline data pervadins „Power Query“, kad gautumėte ir transformuotumėte programose „Excel 2010“ ir „Excel 2013“.
Šis nemokamas priedas yra toks nuostabus, kad apie jį galėtų būti visa knyga. Bet kaip vieną iš 40 geriausių patarimų noriu aptarti labai paprastą dalyką: failų sąrašo įtraukimą į „Excel“, failo sukūrimo datą ir galbūt dydį. Tai naudinga kuriant biudžeto darbaknygių ar nuotraukų sąrašą.
„Excel 2016“ pasirinkite Duomenys, Nauja užklausa, Iš failo, Iš aplanko. Ankstesnėse „Excel“ versijose naudokite „Power Query“, iš failo, iš aplanko. Nurodykite aplanką:
Redaguodami užklausą, dešiniuoju pelės mygtuku spustelėkite nenorimus stulpelius ir pasirinkite Pašalinti.
Norėdami gauti failo dydį, stulpelyje Atributai spustelėkite šią piktogramą:
Parodomas papildomų atributų sąrašas. Pasirinkite Dydis.
Galimas didelis transformavimo parinkčių sąrašas.
Jei baigsite redaguoti užklausą, spustelėkite Uždaryti ir įkelti.
Duomenys į „Excel“ įkeliami kaip lentelė.
Vėliau, norėdami atnaujinti lentelę, pasirinkite Duomenys, Atnaujinti viską. „Excel“ prisimena visus veiksmus ir atnaujina lentelę pagal dabartinį aplanke esančių failų sąrašą.
Norėdami išsamiai aprašyti funkciją, anksčiau žinomą kaip „Power Query“, žiūrėkite Kento Pulso ir Miguelio Escobaro „M“ („Data“ Monkey “).
M skirtas (DUOMENŲ) PELDYKLĖMS »
Ačiū Migueliui Escobarui, Robui Garciai, Mike'ui Girvinui, Ray'ui Hauseriui ir Colinui Michaelui už „Power Query“ nominavimą.
Žiūrėti video
- „Power Query“ įrankiai yra „Excel 2016“ skirtuke Duomenys
- Nemokamas 2010 ir 2013 m. Priedas
- Išvardykite visus failus iš aplanko į „Excel“ tinklelį naudodami „Power Query“
- Pasirinkite Nauja užklausa, iš failo, iš aplanko
- Neaišku: išplėskite atributų lauką, kad gautumėte dydį
- Jei jūsų duomenys yra CSV failuose, visus failus vienu metu galite importuoti į vieną tinklelį
- Paaukštinkite antraštės eilutę
- Ištrinkite likusias antraštės eilutes
- Pakeiskite "" į nulį
- Užpildykite kontūro rodinį
- Ištrinkite bendrą sumos stulpelį
- Atšaukite duomenis
- Formulė, leidžianti konvertuoti mėnesių pavadinimus į datas
- Išsamus žingsnių sąrašas - didžiausias pasaulyje anuliuoti
- Kitą dieną - atnaujinkite užklausą, kad atliktumėte visus veiksmus iš naujo
Vaizdo įrašo nuorašas
- „Power Query“ yra įmontuota į „Windows 2016“ „Excel 2016“ versijas. Ieškokite grupės Gauti ir transformuoti skirtuke Duomenys Jei turite 2010 m
- 2013, jei naudojate „Windows“
- o ne „Mac“ viskas, kas čia yra „Gauti ir transformuoti“
- galite nemokamai atsisiųsti iš „Microsoft“. Tiesiog ieškokite
- Atsisiųskite „Power Query“.
- Šiandien mane domina „Power Query“ naudojimas norint gauti failų sąrašą. Aš
- norite išvardyti visus aplanko failus.
- Gal man reikia pamatyti, kurie failai yra
- didelius failus arba man reikia rūšiuoti arba man reikia
- jūs žinote, kad gautumėte savo derinį
- žinoti biudžeto failus, kuriuos išsiuntėme
- o paskui kitą aplanką
- mes grįžome.
- Norėdami pradėti, eikite į Duomenys, Gauti ir Tranformuoti, Iš failo, Iš aplanko.
- Įklijuokite aplanko kelią arba naudokite mygtuką Naršyti.
- Spustelėkite Gerai ir jie man tai parodys
- peržiūra. Pasirinkite Redaguoti.
- Keletas dalykų, kuriuos matote, mes turime
- failas pavadina plėtinį data
- pasiekta, pakeista data, sukūrimo data.
- Tikrai nėra akivaizdu, kad šis simbolis šalia atributų antraštės reiškia Išplėsti. Spustelėkite tą simbolį ir jame bus daugiau dalykų
- čia ir jei spustelėsite šį simbolį, tada aš
- gali įeiti ir gauti tokius dalykus kaip failo dydis
- arba jei jis yra tik skaitomas ir panašūs dalykai
- kad tokiu atveju noriu tik bylos
- dydžio. Pasirinkite Failo dydis. Spustelėkite gerai. Jie suteikia jums naują lauką su atributų pavadinimu. Dydis.
- Matau, kiek yra baitų
- kiekvieną failą.
- Gal man čia ne viskas reikalinga
- Man nereikia sukurtos datos, kad galėčiau
- dešiniuoju pelės mygtuku spustelėkite ir pasakykite, kad aš noriu
- pašalinti tą stulpelį. Tai
- Dvejetainis failas man nereikalingas, kuris pašalins
- tą stulpelį. Juostelėje spustelėkite Uždaryti ir įkelti.
- Po kelių sekundžių pamatysite rūšiuotą vaizdą
- viskas tame aplanke, jei aplankas
- pokyčių galiu ateiti čia ir galiu
- atnaujinkite užklausą ir ji grįš atgal
- ir ištraukite tuos duomenis teisingai
- man tai yra problema, kurią mes įpratome
- turėtume visą laiką, mes išsiųstume 200
- biudžeto bylos
- ir tu grąžini ką nors ne visus
- atgal reikia mokėti taip palyginti
- dabar iš esmės galiu atlikti „vlookup“
- tarp aplankų.
- Tiesiog nuostabu, kaip
- šaunu, bet žiūrėkime, eikime toliau
- what I have in the book and show you how
- that's just the tip of the iceberg.
- I'm going to create another query. Data, New Query, From File, From Folder.
- I'll copy that folder path here.
- click edit.
- As of October 2016, this trick only works with CSV
- files, but in 2017 it was updated to work with single-sheet Excel files. I
- have a folder a whole bunch of files and
- I want to create one excel grid with all
- of the data from all of these files.
- It's not intuitive at all. Look next to the heading for the Binary column. There is an icon with two arrows pointing down at a horizontal line.
- Click that.
- BAM! it just pulled in every single record from
- every single file in that folder!
- Isn't
- that amazing I mean that was a VBA macro
- before and it takes months to learn VBA
- macros you can learn power query in ten
- minutes.
- We have to select this column and
- go to replace values say that we're
- going to
- replace nothing with the word null click
- okay
- That'll give us Nulls in place of empty cells.
- Those nulls allow us to use this amazing
- featured called Fill Down. Watch that
- column when I choose Fill Down. BAM it
- just pulled in all of that outline view
- and brought the value down.
- I don't need the Grand Total column.
- Right-click and remove.
- Now at this point you say oh yeah hey we could
- pull this in and it'd be awesome. But if
- we wanted to create a pivot table from
- this data having a repeating group going
- across Jan Feb Mar is not a good format
- for a pivot tables.
- Right now we have 47
- rows I need to have 47 times twelve rows
- and to do this in a regular Excel file
- it is horrendous using a Multiple
- Consolidation Range that I learned from
- Mike Alexander at Data Pig Technologies.
- But it is easy in Power Query. Check this out I'm going to choose the
- label columns along the left. These are the things that I don't
- want to change and then on the Transform tab, choose Unpivot Other Columns.
- We go from 47 rows to 564 rows
- that's an amazing step.
- Here you can see
- that these values are text. It is easy enough to
- change it to either currency or a whole
- number. Right click the heading and choose Rename and call it
- revenue
- How about these months? They're
- all text such as Jan, Feb, Mar. Here's an awesome way to fix
- that we go to add column add a custom
- column doesn't matter what the name you use.
- The calculation, in quotes, is " 1, 2016". Click OK.
- Now we have this new custom column I'm going to take the
- attribute column containing Month names and the new custom column. Select both columns
- and say I want to merge those columns
- with a space in between and call it date.
- Click OK. That looks enough
- like a real date that when I go to
- transform and change it to a date it
- converts it to a true Excel date.
- At this point these two temporary
- columns I can right click and remove.
- Now you could be saying to yourself:
- Wait, Bill! we could have done all of this in Excel and that's absolutely true we could have done all of it in Excel it would have been harder to get all the CSV files into one file it would have taken longer to fill in the blanks it definitely would have taken longer to do the unpivot operation but here's the thing look over on the right-hand side we haven't talked about Applied Steps at all. The Applied Steps panel is like the world's greatest undo if you need an audit trail if the auditors come and say well how did you get from all these CSV files to this file that we're building our financial statements on you can go back and show what it looked like at each step along the way. If you screwed something up back here you could change or edit that step. Next, on the Power Query Home tab, choose Close and Load. So here's our data set this is based on all of the files in this folder. Let's build a little pivot table from here insert pivot table existing worksheet right here and I'll put revenue in the values area products down the left hand side you see that we have six million in revenue. Well that's today's data now tomorrow tomorrow let's say that we get a couple of new customers a couple of new files come along and our IT department takes those and dumps them into our folder all I have to do is reopen this file select the query come over here and refresh and then come here analyze refresh the pivot table and we have the new data! Power Query is faster on day one maybe by a factor of 20-30%. On day two is faster by 99% it's an absolutely amazing product Power Query. It's in Excel 2016 but if you're in 2010 or 2013 for Windows you're more than welcome to go out and download it for free. The book that will teach you about Power Query is M is for (Data) Monkey" by Ken Puls and Miguel Escobar.
- Ši knyga mokys
- jums viską apie maitinimo užklausą
- sąsaja tai nuostabi knyga geriausia
- knyga apie galios užklausą viską, ką išmokau
- Aš sužinojau iš šios knygos. Išlipau iš
- Orlandas į Dalasą - perskaičiau visą knygą
- ir mano žinios apie galios užklausą
- išaugo per dvi valandas galite būti iki
- greitį ir pakeiskite daiktus, kuriuos norėtumėte
- buvo įpratę daryti su VBA.
Atsisiųsti failą
Parsisiųskite failo pavyzdį čia: Podcast2037.xlsx
