A Power Query a Microsoft új eszköze az adatok kinyerésére, átalakítására és betöltésére. A mai cikk egy mappa összes fájljának feldolgozásáról szól.
A Power Query be van építve az Excel 2016-ba, és ingyenesen letölthető az Excel 2010 és az Excel 2013 bizonyos verzióiban. Az eszközt úgy tervezték, hogy különféle forrásokból nyerje ki, alakítsa át és töltse be az adatokat az Excelbe. A legjobb rész: A Power Query megjegyzi a lépéseit, és lejátszja őket, amikor frissíteni szeretné az adatokat. Amint ez a könyv nyomdába kerül, az Excel 2016 Power Query szolgáltatásai az Adatok fülön, a Get & Transform csoport Új lekérdezés részében találhatók. Nehéz megjósolni, hogy a Microsoft visszamenőlegesen átnevezi-e a Power Query for Get & Transform programot az Excel 2010 és az Excel 2013 programban.
Ez az ingyenes kiegészítő olyan csodálatos, hogy lehet egy egész könyv róla. De a 40 legnépszerűbb tippem egyikeként valami nagyon egyszerű dolgot szeretnék bemutatni: a fájlok listájának az Excelbe történő bevitelét, a fájl létrehozásának dátumával és esetleg a méretével együtt. Ez hasznos a költségvetési munkafüzetek vagy a fényképek listájának létrehozásához.
Az Excel 2016 programban az Adatok, az Új lekérdezés, a Fájlból, a Mappából lehetőséget választja. A korábbi Excel verziókban használja a Power Query, a Fájlból, a Mappából parancsot. Adja meg a mappát:
A lekérdezés szerkesztése közben kattintson a jobb gombbal minden kívánt oszlopra, és válassza az Eltávolítás lehetőséget.
A Fájlméret megszerzéséhez kattintson erre az ikonra az Attribútumok oszlopban:
Megjelenik az extra attribútumok listája. Válassza a Méret lehetőséget.
A Transform opciók nagy listája érhető el.
Ha végzett a lekérdezés szerkesztésével, kattintson a Bezárás és betöltés gombra.
Az adatok táblázatként töltődnek be az Excelbe.
Később a táblázat frissítéséhez válassza az Adatok, az Összes frissítése lehetőséget. Az Excel megjegyzi az összes lépést, és frissíti a táblázatot a mappa aktuális fájllistájával.
A korábban Power Query néven ismert funkció teljes leírását itt találja: M is (Data) Monkey, Ken Puls és Miguel Escobar.
M a (DATA) MAJOM számára »
Köszönet Miguel Escobarnak, Rob Garciának, Mike Girvinnek, Ray Hausernek és Colin Michaelnek a Power Query jelöléséért.
Videót nézni
- A Power Query eszközök az Excel 2016 Adatok lapján találhatók
- Ingyenes bővítmény 2010-re és 2013-ra
- Sorolja fel az összes fájlt egy mappából az Excel rácsba a Power Query használatával
- Válassza az Új lekérdezés, fájlból, mappából lehetőséget
- Nem nyilvánvaló: bontsa ki az attribútum mezőt a méret megszerzéséhez
- Ha az adatai CSV-fájlokban vannak, akkor az összes fájlt egyszerre egyetlen rácsba importálhatja
- Hirdesse a címsort
- Törölje a fennmaradó fejlécsorokat
- Cserélje a "" nullára
- Töltse ki a vázlat nézetet
- Törölje az összesített oszlopot
- Távolítsa el az adatokat
- Képlet a hónapnevek dátumokká alakítására
- A lépések teljes listája - a világ legnagyobb visszavonása
- Másnap - frissítse a lekérdezést az összes lépés újbóli elvégzéséhez
A videó átirata
- A Power Query az Excel 2016 Windows-verzióiba van beépítve. Keresse meg az Adatok lapot a Szerzés és átalakítás csoportban. Ha van 2010 ill
- 2013, amíg a Windows rendszert futtatja
- és nem Mac minden, ami itt található a Get & Transform-ban
- ingyen letöltheti a Microsoft-tól. Csak keressen rá
- Töltse le a Power Query alkalmazást.
- Ma érdekel, hogy a Power Query segítségével fájllistát kapok. én
- fel akarja sorolni az összes fájlt egy mappába.
- Talán meg kell látnom, melyek a fájlok
- nagy fájlokat, vagy rendeznem kell, vagy szükségem van
- tudod, hogy kapsz egy kombinációt belőled
- ismeri az általunk kiküldött költségvetési fájlokat
- majd egy másik mappát melyiket
- visszajöttünk.
- Első lépésként lépjen az Adatok, Letöltés és átalakítás, Fájlból, Mappa mappába.
- Illessze be a mappa elérési útjába, vagy használja a Tallózás gombot.
- Kattintson az OK gombra, és ezt megmutatják nekem
- előnézet. Válassza a Szerkesztés lehetőséget.
- Néhány dolog itt van, amit lát
- a fájl neve a kiterjesztés dátuma
- hozzáférés, módosított dátum, létrehozás dátuma.
- Valójában nem nyilvánvaló, hogy ez a szimbólum az Attribútumok címsor mellett kibontást jelent. Kattintson erre a szimbólumra, és még több dolog van benne
- itt és ha erre a szimbólumra kattint, akkor én
- bemehet, és beszerezhet például fájlméretet
- vagy ha csak olvasható és hasonlók
- hogy ebben az esetben csak fájlt akarok
- méret. Válassza a Fájlméret lehetőséget. Kattintson az OK gombra. Új mezőt adnak Attributes.Size névvel.
- Látom, hány bájt van
- minden fájlt.
- Talán nincs szükségem itt mindenre
- Nincs szükségem a létrehozás dátumára, hogy tudjak
- kattintson jobb gombbal, és mondja, hogy szeretném
- távolítsa el azt az oszlopot. Ez
- bináris nincs szükségem arra, hogy eltávolítsam
- azt az oszlopot. A szalagon kattintson a Bezárás és betöltés gombra.
- Néhány másodperc múlva rendezhető képet kap
- minden abban a mappában, ha a mappa
- változások, ide bejöhetek és tudok
- frissítse a lekérdezést, és vissza fog térni
- és húzza ki ezeket az adatokat
- számomra ez a probléma, amit szoktunk
- van annyi időnk, hogy 200-at küldünk
- költségvetési fájlok
- és valakit visszakapsz, nem mindet
- vissza kell tudni hasonlítani
- most lényegében vlookupot tudok csinálni
- mappák között.
- Csak csodálatos, hogyan
- klassz, de nézzük, lépjünk túl
- 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.
- Ez a könyv megtanítja
- mindent a teljesítmény lekérdezéssel kapcsolatban
- felület ez egy csodálatos könyv a legjobb
- könyv a hatalom lekérdezéséről mindent, amit tanultam
- Ebből a könyvből tanultam. -Ból indultam repülésre
- Orlando Dallasba - elolvastam az egész könyvet
- és a hatalom lekérdezésének ismerete csak
- két óra alatt szárnyalhatsz
- sebességet, és cserélje ki azokat a dolgokat, amelyeket szeretne
- a VBA-val szokott dolgozni.
Fájl letöltése
Töltse le a minta fájlt innen: Podcast2037.xlsx
