Power Query ir jauns Microsoft rīks datu iegūšanai, pārveidošanai un ielādēšanai. Šodienas raksts ir par visu mapē esošo failu apstrādi.
Power Query ir iebūvēts programmā Excel 2016 un ir pieejams kā bezmaksas lejupielāde noteiktās Excel 2010 un Excel 2013. versijās. Šis rīks ir paredzēts datu iegūšanai, pārveidošanai un ielādēšanai programmā Excel no dažādiem avotiem. Labākā daļa: Power Query atceras jūsu soļus un atskaņos tos, kad vēlaties atsvaidzināt datus. Kad šī grāmata tiek iespiesta, programmas Power 2016 vaicājuma funkcijas atrodas cilnes Dati grupas Iegūt un pārveidot sadaļā Jauns vaicājums. Ir grūti paredzēt, vai Microsoft ar atpakaļejošu spēku pārdēvēs Power Query, lai iegūtu un pārveidotu programmā Excel 2010 un Excel 2013.
Šī bezmaksas pievienojumprogramma ir tik pārsteidzoša, ka par to varētu būt visa grāmata. Bet kā vienu no 40 labākajiem padomiem es vēlos aplūkot kaut ko ļoti vienkāršu: failu saraksta ievešana programmā Excel kopā ar faila izveides datumu un varbūt arī lielumu. Tas ir noderīgi, lai izveidotu budžeta darbgrāmatu sarakstu vai fotoattēlu sarakstu.
Programmā Excel 2016 atlasāt Dati, Jauns vaicājums, No faila, No mapes. Iepriekšējās Excel versijās izmantojiet Power Query, From File, From Folder. Norādiet mapi:
Rediģējot vaicājumu, ar peles labo pogu noklikšķiniet uz nevēlamām slejām un izvēlieties Noņemt.
Lai iegūtu faila lielumu, slejā Atribūti noklikšķiniet uz šīs ikonas:
Parādās papildu atribūtu saraksts. Izvēlieties Izmērs.
Ir pieejams liels pārveidošanas iespēju saraksts.
Kad esat pabeidzis vaicājuma rediģēšanu, noklikšķiniet uz Aizvērt un ielādēt.
Dati programmā Excel tiek ielādēti kā tabula.
Vēlāk, lai atjauninātu tabulu, atlasiet Dati, Atsvaidzināt visu. Excel atceras visas darbības un atjaunina tabulu ar pašreizējo mapē esošo failu sarakstu.
Lai iegūtu pilnīgu funkcijas, kas agrāk bija zināma kā Power Query, aprakstu, skatiet M is for (Data) Monkey, ko izstrādājuši Ken Puls un Miguel Escobar.
M ir paredzēts (DATU) PĒRKIEM »
Paldies Migelam Eskobaram, Robam Garsijai, Maikam Girvinam, Rejam Hauseram un Kolinam Maiklam par Power Query izvirzīšanu.
Skatīties video
- Power Query rīki atrodas cilnē Dati programmā Excel 2016
- Bezmaksas pievienojumprogramma 2010. un 2013. gadam
- Uzskaitiet visus failus no mapes Excel režģī, izmantojot Power Query
- Izvēlieties Jauns vaicājums, no faila, no mapes
- Nav acīmredzams: paplašiniet atribūtu lauku, lai iegūtu izmēru
- Ja jūsu dati ir CSV failos, visus failus vienlaikus varat importēt vienā režģī
- Paaugstiniet virsraksta rindu
- Izdzēsiet atlikušās galvenes rindas
- Nomainiet "" ar nulli
- Aizpildiet kontūras skatu
- Dzēst kolonnu kopsummu
- Atcelt datu pagriešanu
- Formula, lai mēneša nosaukumus pārvērstu datumos
- Pilns darbību saraksts - pasaulē vislielākā atsaukšana
- Nākamā diena - atsvaidziniet vaicājumu, lai atkārtoti veiktu visas darbības
Video atšifrējums
- Power Query ir iebūvēts programmas Excel 2016. Windows versijās. Grupas Iegūt un pārveidot cilnē Dati. Ja jums ir 2010 vai
- 2013, kamēr jūs izmantojat Windows
- un ne Mac viss, kas atrodas šeit Get & Transform
- jūs varat lejupielādēt bez maksas no Microsoft. Vienkārši meklējiet
- Lejupielādējiet Power Query.
- Šodien esmu ieinteresēts izmantot Power Query, lai iegūtu failu sarakstu. Es
- vēlaties uzskaitīt visus mapē esošos failus.
- Varbūt man ir nepieciešams redzēt, kuri faili ir
- lieli faili vai man ir jākārto vai man ir nepieciešams
- jūs zināt, lai iegūtu jūsu kombināciju
- zināt budžeta failus, kurus mēs izsūtījām
- un pēc tam citu mapi
- mēs atgriezāmies.
- Lai sāktu, dodieties uz Dati, Iegūt un pārveidot, No faila, No mapes.
- Ielīmējiet mapes ceļā vai izmantojiet pogu Pārlūkot.
- Noklikšķiniet uz Labi, un viņi man to parāda
- priekšskatījums. Izvēlieties Rediģēt.
- Pāris lietas šeit jūs redzat
- faila nosaukums paplašinājumam ir datums
- piekļūt, modificēšanas datums, izveidošanas datums.
- Tas tiešām nav acīmredzami, ka šis simbols blakus virsrakstam Atribūti nozīmē Izvērst. Noklikšķiniet uz šī simbola, un tajā būs vairāk lietu
- šeit un, ja jūs noklikšķiniet uz šī simbola, tad es
- var ieiet un iegūt tādas lietas kā faila lielums
- vai ja tas ir tikai lasāms un tamlīdzīgi
- ka šajā gadījumā es tikai vēlos failu
- Izmērs. Izvēlieties Faila lielums. Noklikšķiniet uz Labi. Viņi jums piešķir jaunu lauku ar nosaukumu Atribūti. Izmērs.
- Es redzu, cik baitu ir
- katru failu.
- Varbūt man šeit viss nav vajadzīgs
- Man nav vajadzīgs izveidots datums, lai es varētu
- ar peles labo pogu noklikšķiniet un sakiet, ka es to vēlos
- noņemt šo kolonnu. Šis
- binārs, kas man nav vajadzīgs, noņems
- šo kolonnu. Lentē noklikšķiniet uz Aizvērt un ielādēt.
- Pēc dažām sekundēm jums būs kārtojams skats
- viss šajā mapē, ja mape
- izmaiņas es varu ienākt šeit un es varu
- atsvaidziniet vaicājumu, un tas atgriezīsies
- un izvelciet šos datus pareizi
- man tā ir problēma, kuru mēs mēdzām izmantot
- visu laiku mēs izsūtīsim 200
- budžeta faili
- un jūs atgūstat kādu, nevis visus
- jums jāspēj to salīdzināt
- tagad es būtībā varu izdarīt vlookup
- starp mapēm.
- Tas ir vienkārši pārsteidzoši, kā
- forši tas ir, bet skatīsimies, kas tiks tālāk
- 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.
- Šī grāmata iemācīs
- jums visu par enerģijas vaicājumu
- interfeiss tā ir pārsteidzoša grāmata vislabāk
- grāmata par enerģijas vaicājumu visu, ko es uzzināju
- Es iemācījos no šīs grāmatas. Es iekāpu lidojumā no plkst
- Orlando uz Dalasu - es izlasīju visu grāmatu
- un manas zināšanas par enerģijas pieprasījumu tikai
- pieauga divās stundās, jūs varat būt līdz
- ātrumu un nomainiet lietas, kuras jūs vēlētos
- ir pieraduši darīt ar VBA.
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Podcast2037.xlsx
