Power Query is een nieuwe tool van Microsoft om gegevens te extraheren, transformeren en laden. Het artikel van vandaag gaat over het verwerken van alle bestanden in een map.
Power Query is ingebouwd in Excel 2016 en is beschikbaar als gratis download in bepaalde versies van Excel 2010 en Excel 2013. De tool is ontworpen om gegevens uit verschillende bronnen te extraheren, om te zetten en in Excel te laden. Het beste deel: Power Query onthoudt uw stappen en speelt ze af wanneer u de gegevens wilt vernieuwen. Terwijl dit boek ter perse gaat, bevinden de Power Query-functies in Excel 2016 zich op het tabblad Gegevens, in de groep Ophalen en transformeren, onder Nieuwe query. Het is moeilijk te voorspellen of Microsoft Power Query met terugwerkende kracht zal hernoemen naar Get & Transform in Excel 2010 en Excel 2013.
Deze gratis add-in is zo geweldig dat er een heel boek over kan bestaan. Maar als een van mijn 40 beste tips wil ik iets heel eenvoudigs behandelen: een lijst met bestanden naar Excel brengen, samen met de aanmaakdatum van het bestand en misschien de grootte. Dit is handig voor het maken van een lijst met budgetwerkmappen of een lijst met foto's.
In Excel 2016 selecteert u Gegevens, Nieuwe query, Uit bestand, Uit map. Gebruik in eerdere Excel-versies Power Query, Uit bestand, Uit map. Specificeer de map:
Klik tijdens het bewerken van de query met de rechtermuisknop op kolommen die u niet wilt en kies Verwijderen.
Om de bestandsgrootte te zien, klikt u op dit pictogram in de kolom Kenmerken:
Er verschijnt een lijst met extra attributen. Kies de maat.
Er is een grote lijst met transformatie-opties beschikbaar.
Als u klaar bent met het bewerken van de query, klikt u op Sluiten en laden.
De gegevens worden als een tabel naar Excel geladen.
Om de tabel later bij te werken, selecteert u Gegevens, Alles vernieuwen. Excel onthoudt alle stappen en werkt de tabel bij met een actuele lijst met bestanden in de map.
Voor een volledige beschrijving van de functie die voorheen bekend stond als Power Query, ga je naar M is for (Data) Monkey van Ken Puls en Miguel Escobar.
M is voor (DATA) MONKEY »
Met dank aan Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser en Colin Michael voor het nomineren van Power Query.
Bekijk video
- De Power Query-tools bevinden zich op het tabblad Gegevens in Excel 2016
- Gratis add-in voor 2010 en 2013
- Maak een lijst van alle bestanden uit een map in het Excel-raster met Power Query
- Kies Nieuwe zoekopdracht, Uit bestand, Uit map
- Niet voor de hand liggend: vouw het kenmerkveld uit om de grootte te krijgen
- Als uw gegevens in CSV-bestanden staan, kunt u alle bestanden tegelijk in één raster importeren
- Promoot de koprij
- Verwijder de resterende koptekstrijen
- Vervang "" door null
- Vul in voor de overzichtsweergave
- Verwijder de kolom met het eindtotaal
- Draai de gegevens los
- Formule om maandnamen om te zetten in datums
- Volledige lijst met stappen - 's werelds grootste ongedaan maken
- Volgende dag - vernieuw de query om alle stappen opnieuw uit te voeren
Transcript van de video
- Power Query is ingebouwd in Windows-versies van Excel 2016. Kijk op het tabblad Gegevens in de groep Ophalen en transformeren. Als u 2010 of
- 2013 zolang u Windows gebruikt
- en niet Mac alles wat hier in Ophalen en transformeren is
- u kunt gratis downloaden van Microsoft. Zoek gewoon naar
- Download Power Query.
- Vandaag ben ik geïnteresseerd in het gebruik van Power Query om een lijst met bestanden te krijgen. ik
- wil alle bestanden in een map weergeven.
- Misschien moet ik zien welke bestanden de
- grote bestanden of ik moet sorteren of ik heb
- je weet dat je een combinatie van jezelf moet krijgen
- ken de budgetbestanden die we hebben verzonden
- en dan een andere map welke
- we kwamen terug.
- Ga om te beginnen naar Gegevens, Ophalen en transformeren, Uit bestand, Uit map.
- Plak in het mappad of gebruik de knop Bladeren.
- Klik op OK en ze laten me dit zien
- voorbeeld. Kies Bewerken.
- U ziet hier een paar dingen die we hebben
- de bestandsnaam de extensie de datum
- geopend, datum gewijzigd, datum gemaakt.
- Het is echt niet duidelijk dat dit symbool naast de kop Attributen Uitbreiden betekent. Klik op dat symbool en er zit meer spullen in
- hier en als je op dit symbool klikt, dan ik
- kan naar binnen gaan en zaken als bestandsgrootte ophalen
- of als het alleen-lezen is en dergelijke
- dat dus in dit geval wil ik gewoon een bestand
- grootte. Kies Bestandsgrootte. Klik OK. Ze geven je een nieuw veld met de naam Attributes.Size.
- Ik kan zien hoeveel bytes er zijn
- elk bestand.
- Misschien heb ik hier misschien niet alles nodig
- Ik heb de gecreëerde datum niet nodig, dus ik kan het
- klik met de rechtermuisknop en zeg dat ik wil
- verwijder die kolom. Deze
- binair Ik heb niet nodig dat zal verwijderen
- die kolom. Klik op het lint op Sluiten en laden.
- Binnen een paar seconden heb je een sorteerbaar overzicht van
- alles in die map als de map
- veranderingen Ik kan hier binnenkomen en ik kan het
- vernieuw de zoekopdracht en deze keert terug
- eruit en trek die gegevens er goed uit, dit is
- voor mij is dit een probleem dat we vroeger hadden
- hebben alle tijd die we zouden sturen 200
- budget bestanden
- en je krijgt iemand niet allemaal terug
- terug moet je dus kunnen vergelijken
- nu kan ik in wezen een vlookup doen
- tussen mappen.
- Het is gewoon verbazingwekkend hoe
- cool is het maar kijk, laten we verder gaan
- 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.
- Dit boek zal je leren
- u alles over de machtsvraag
- interface het is een geweldig boek het beste
- boek over macht vraag alles wat ik heb geleerd
- Ik heb uit dit boek geleerd. Ik ben op een vlucht van
- Orlando naar Dallas - Ik heb het hele boek gelezen
- en mijn kennis van machtsvraag gewoon
- gestegen in twee uur kun je maximaal zijn
- snelheid en vervang dingen die u zou willen
- hebben een keer gedaan met VBA.
Download bestand
Download het voorbeeldbestand hier: Podcast2037.xlsx
