Power Query, Microsoft'tan Veri Ayıklama, Dönüştürme ve Yükleme için yeni bir araçtır. Bugünün makalesi, bir klasördeki tüm dosyaların işlenmesi hakkındadır.
Power Query, Excel 2016'da yerleşiktir ve Excel 2010 ve Excel 2013'ün belirli sürümlerinde ücretsiz olarak indirilebilir. Araç, çeşitli kaynaklardan verileri ayıklamak, dönüştürmek ve Excel'e yüklemek için tasarlanmıştır. En iyi bölüm: Power Query adımlarınızı hatırlar ve verileri yenilemek istediğinizde bunları yürütür. Bu kitap baskıya giderken, Excel 2016'daki Power Query özellikleri, Veri sekmesinde, Al ve Dönüştür grubunda, Yeni Sorgu altında yer alıyor. Microsoft'un geriye dönük olarak Power Query'yi Excel 2010 ve Excel 2013'te Al ve Dönüştür olarak yeniden adlandırıp adlandırmayacağını tahmin etmek zordur.
Bu ücretsiz eklenti o kadar şaşırtıcı ki, bununla ilgili bir kitap olabilir. Ancak en iyi 40 ipucumdan biri olarak, çok basit bir şeyi ele almak istiyorum: dosya oluşturma tarihi ve belki boyutu ile birlikte dosyaların bir listesini Excel'e getirmek. Bu, bütçe çalışma kitapları listesi veya fotoğraf listesi oluşturmak için kullanışlıdır.
Excel 2016'da Veri, Yeni Sorgu, Dosyadan, Klasörden'i seçersiniz. Önceki Excel sürümlerinde, Power Query, Dosyadan, Klasörden kullanın. Klasörü belirtin:
Sorguyu düzenlerken, istemediğiniz herhangi bir sütuna sağ tıklayın ve Kaldır'ı seçin.
Dosya Boyutunu almak için, Özellikler sütunundaki bu simgeye tıklayın:
Ekstra niteliklerin bir listesi görünür. Boyut seçin.
Geniş bir Dönüştürme seçenekleri listesi mevcuttur.
Sorguyu düzenlemeyi tamamladığınızda Kapat ve Yükle'yi tıklayın.
Veriler Excel'e tablo olarak yüklenir.
Daha sonra tabloyu güncellemek için Veri, Tümünü Yenile'yi seçin. Excel, tüm adımları hatırlar ve tabloyu klasördeki geçerli dosya listesiyle günceller.
Daha önce Power Query olarak bilinen özelliğin tam bir açıklaması için Ken Puls ve Miguel Escobar'ın M is for (Data) Monkey'sine göz atın.
M (VERİ) MAYMUN içindir »
Miguel Escobar, Rob Garcia, Mike Girvin, Ray Hauser ve Colin Michael'a Power Query'yi aday gösterdikleri için teşekkürler.
Video izle
- Power Query araçları, Excel 2016'da Veri sekmesindedir
- 2010 ve 2013 için ücretsiz eklenti
- Power Query kullanarak bir klasördeki tüm dosyaları Excel kılavuzunda listeleyin
- Dosyadan, Klasörden Yeni Sorgu Seçin
- Açık değil: boyut almak için öznitelik alanını genişletin
- Verileriniz CSV dosyalarındaysa, tüm dosyaları aynı anda tek bir ızgaraya aktarabilirsiniz.
- Başlık satırını yükseltin
- Kalan başlık satırlarını silin
- "" Null ile değiştirin
- Anahat görünümü için doldurun
- Genel toplam sütununu silin
- Verilerin özetini kaldırın
- Ay adlarını tarihlere dönüştürmek için formül
- Adımların tam listesi - dünyanın en büyük Geri Al
- Ertesi gün - tüm adımları yeniden uygulamak için sorguyu yenileyin
Videonun Transkripti
- Power Query, Excel 2016'nın Windows sürümlerinde yerleşiktir. Al ve Dönüştür grubundaki Veri sekmesine bakın. 2010'unuz varsa veya
- Windows çalıştırdığınız sürece 2013
- ve Mac'te değil, Get & Transform'de bulunan her şey
- Microsoft'tan ücretsiz olarak indirebilirsiniz. Sadece arayın
- Power Query'yi indirin.
- Bugün, bir dosya listesi almak için Power Query kullanmakla ilgileniyorum. ben
- bir klasördeki tüm dosyaları listelemek istiyorum.
- Belki de hangi dosyaların
- büyük dosyalar veya sıralamam gerekiyor veya ihtiyacım var
- senden bir kombinasyon almayı biliyorsun
- gönderdiğimiz bütçe dosyalarını bilin
- ve sonra farklı bir klasör olan
- geri geldik.
- Başlamak için Veri, Al ve Aktar, Dosyadan, Klasörden seçeneğine gidin.
- Klasör yoluna yapıştırın veya Gözat düğmesini kullanın.
- Tamam'ı tıklayın ve bana bunu gösteriyorlar
- Ön izleme. Düzenle'yi seçin.
- Burada gördüğünüz birkaç şey var
- dosya adı uzantı tarih
- erişildi, değiştirilme tarihi, oluşturulma tarihi.
- Nitelikler başlığının yanındaki bu sembolün Genişlet anlamına geldiği gerçekten açık değil. Bu sembole tıklayın ve içinde daha fazla şey var
- burada ve bu sembole tıklarsanız o zaman ben
- içeri girip dosya boyutu gibi şeyler alabilir
- veya salt okunursa ve
- bu yüzden bu durumda sadece dosya istiyorum
- boyut. Dosya Boyutu'nu seçin. Tamam'ı tıklayın. Size Attributes.Size adıyla yeni bir alan verirler.
- İçinde kaç bayt olduğunu görebiliyorum
- her dosya.
- Belki burada her şeye ihtiyacım yok belki
- Oluşturulan tarihe ihtiyacım yok, böylece yapabilirim
- sağ tıkla ve istediğimi söyle
- o sütunu kaldırın. Bu
- ikili dosyaya ihtiyacım yok kaldıracak
- o sütun. Şeritten Kapat ve Yükle'ye tıklayın.
- Birkaç saniye içinde, sıralanabilir bir görünüm elde edeceksiniz.
- o klasördeki her şey, eğer klasör
- değişiklikler buraya gelebilirim ve yapabilirim
- sorguyu yenileyin ve geri dönecektir
- çıkarıp o veriyi doğru çekin bu
- benim için bu eskiden yaptığımız bir problem
- 200 göndereceğimiz her zaman var
- bütçe dosyaları
- ve birini geri alıyorsun hepsini değil
- Geride karşılaştırabilmen gerekiyor
- şimdi esasen bir vlookup yapabilirim
- klasörler arasında.
- Nasıl olması harika
- havalı ama bak hadi ötesine geçelim
- 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.
- Bu kitap öğretecek
- güç sorgusuyla ilgili her şey
- arayüz harika bir kitap, en iyisi
- güç sorgusu üzerine kitap, öğrendiğim her şey
- Bu kitaptan öğrendim. Uçağa bindim
- Orlando'dan Dallas'a - kitabın tamamını okudum
- ve güç sorgulama bilgim sadece
- iki saat içinde yükselebilirsin
- hızlandırın ve yapacağınız şeyleri değiştirin
- VBA ile yapmıştım.
Dosyayı indir
Örnek dosyayı buradan indirin: Podcast2037.xlsx
