Power Query cukup kuat. Tapi saya akan melakukan Power Query Squared… menulis kueri untuk satu halaman dan kemudian Excel melakukan kueri yang sama untuk seluruh daftar halaman web.
Menonton video
- Trik hari ini diadaptasi dari buku M is for Data Monkey
- Buat kueri untuk mendapatkan data dari satu halaman web
- Edit kueri untuk mengubahnya menjadi fungsi dengan (VariableName) => sebelum Let
- Ubah URL hard-code ke VariableName
- Ubah nama kueri menjadi fxWeather
- Tutup & Muat. Datanya akan hilang.
- Gunakan Excel Trickery untuk membuat tabel semua URL
- Buat kueri dari tabel itu.
- Tambahkan kolom baru Cuaca
=fxWeather((URL)) - Luaskan kolomnya. Hapus centang Prefix
- Luar biasa!
Transkrip Video
Pelajari Excel dari Podcast, Episode 2056: Power Query Squared
Hai, selamat datang kembali di netcast, saya Bill Jelen. Saya menemukan trik ini ketika saya bersiap untuk melakukan seminar di sebuah konferensi di Dallas yang disebut Excelapalooza, nama konferensi Excel terbesar yang pernah ada. Anda harus memeriksanya setiap September di Dallas.
Dan penghargaan untuk Ken Puls dan Miguel Escobar karena saya harus melakukan satu jam untuk pertanyaan tentang kekuatan, jadi tentu saja, saya mengeluarkan buku yang luar biasa, buku terbesar di dunia tentang Power Query. Saya membolak-balik buku dan saya melihat mereka memiliki satu teknik dan saya berkata, "Tunggu sebentar. Saya akan melihat apakah saya bisa mengadaptasi teknik ini. " Dan bagi saya ini luar biasa dalam power query. Dan inilah yang akan kami lakukan, kami akan mengambil satu kueri kekuatan dan kemudian kami akan menjalankan kueri kekuatan itu, kueri itu, puluhan kali, oke?
Jadi, contoh yang saya dapatkan adalah saat saya ingin mengambil data dari halaman web, oke. Dan saya pergi ke - saya hanya mencari beberapa halaman web yang saya bisa - yang dapat saya gunakan sebagai contoh. Saya berakhir di Weather Underground dan inilah URL-nya, dan Anda dapat melihat bahwa saya berada di Dallas-Fort Worth, jadi kami menarik data untuk Dallas dan sepertinya 2 Januari 2015. Jadi di sana di URL adalah parameter , Baik? Dan itu adalah URL yang baru saja matang untuk membuat hal ini berfungsi.
Kami akan melihat sekilas halaman web meskipun tidak begitu penting. Anda lihat ada banyak data yang berbeda di sini di halaman web dan saya memutuskan saya hanya akan mencoba dan mendapatkan curah hujan dan suhu tinggi dan rendah. Dan inilah pertanyaannya. Dan izinkan saya memberi tahu Anda di sini bahwa podcast ini bukan tentang cara Membuat Kueri ini, Kueri Baru, Dari Sumber Lain, Dari Web, menentukan URL dan kemudian sekelompok langkah yang tidak akan saya detailkan di sini untuk mendapatkan jawaban akhir dari Max Temp, Min Temp, dan presipitasi. Intinya adalah Anda akan mengambil kueri Anda sendiri dan membuatnya berfungsi untuk banyak hal.
Jadi, saya klik Tutup & Muat dan kueri ini berfungsi, mengembalikan satu baris saya. Segalanya menarik. Dan saya akan kembali masuk, saya akan Mengedit kueri ini dan saya akan pergi ke Tampilan, Editor Tingkat Lanjut. Saya akan mengambil kueri ini dan saya akan membuatnya menjadi sebuah fungsi, oke? Jadi di sini sebelum kata LET, saya tekan Enter. Dan dalam tanda kurung, saya akan memberikan variabel (MyURL) dan kemudian => panah kecil di sana, oke? Keren. Dan kemudian di sini, di mana mereka memiliki URL dalam tanda kutip, saya ingin membuang seluruh URL termasuk tanda kutip dan kemudian ketik nama variabel saya MyURL, oke. Jadi apa yang kami katakan adalah, kami akan meneruskannya ke URL dan itu akan melakukan kueri yang sama tetapi dengan URL apa pun yang kebetulan kami sampaikan.
Sekarang, ada beberapa hal yang membingungkan di sini, saat saya mengklik Selesai, ya ampun! Semua langkah yang saya terapkan hilang dan mereka ingin saya memasukkan parameter. Abaikan saja semua itu. Kami akan mengganti nama ini; kami akan menyebutnya fxWeather. FX, tentu saja, menjadi singkatan dari function dan Anda harus benar-benar mengingat nama ini dan mengingat huruf mana yang dikapitalisasi, itu akan menjadi sangat penting dalam beberapa menit. Beranda, Tutup & Muat, dan BAM! Semuanya hilang. Oh tidak! Tapi tidak apa-apa. Baiklah, jadi kami tahu itu ada di sana. Ini hanya koneksi. Sekarang, saya akan datang ke sini dan ini hanya Excel lama, oke? Jadi inilah URL-nya, saya membaginya menjadi bagian pertama dari URL, bagian akhir dari URL. Saya tahu bahwa saya perlu mencatat tanggal; Saya perlu memformatnya dalam format tahun yang aneh ini,bulan, dan hari jadi saya menggunakan fungsi TEXT untuk melakukan itu. Masukkan tanggal mulai di sini. Saya bahkan bisa ganti bandara, jadi sekarang saya bolak-balik. Mari lakukan MCO untuk Orlando dan lakukan beberapa data terbaru. Jadi saya akan mulai pada 10/1/2016, baiklah. Jadi sekarang kami memiliki meja kecil yang bagus ini di sini. Dan omong-omong, itu harus berupa meja. Anda harus menggunakan format sebagai tabel atau Ctrl + T. Jadi, Anda tahu, ini hanya mengambil tanggal itu dan kemudian + 1 + 1 + 1. Saya memformatnya, saya membangun URL.Anda harus menggunakan format sebagai tabel atau Ctrl + T. Jadi, Anda tahu, ini hanya mengambil tanggal itu dan kemudian + 1 + 1 + 1. Saya memformatnya, saya membangun URL.Anda harus menggunakan format sebagai tabel atau Ctrl + T. Jadi, Anda tahu, ini hanya mengambil tanggal itu dan kemudian + 1 + 1 + 1. Saya memformatnya, saya membangun URL.
Baiklah sekarang, kita akan membuat kueri dari tabel ini. Baiklah, dan itu informasiku. Saya akan Menambahkan Kolom Baru, Menambahkan Kolom Kustom, kolom akan disebut Cuaca, dan rumusnya adalah = fxWeather. Pastikan itu kasus yang sama persis, huruf besar dan kecil yang sama, dan kami akan memasukkan bidang yang disebut URL seperti itu, menutup tanda kurung. Tidak ada kesalahan sintaks, klik Oke. Mereka ingin tahu tentang Privasi di sini, ini semua Data publik, klik Simpan, oke. Jadi, inilah teman kencan kita. Lucu sekali mereka benar-benar mengubah format saya menjadi sesuatu yang tidak terlihat seperti yang saya mulai. Dan inilah Weather dengan simbol perluas. Jadi saya akan mengklik simbol perluas, hapus centang Gunakan nama kolom asli sebagai awalan. Saya ingin Max, Min, Presipitasi,klik OK. Baiklah, dan sekarang yang saya butuhkan hanyalah tanggal dan informasi di luar sana. Jadi saya akan klik kanan dan hapus kolom ini, klik kanan dan hapus kolom ini. Di sini, saya tidak membutuhkan waktu itu jadi saya akan mengatakan ini hanya kencan, oke. Dan lihat apa yang dilakukannya setiap tanggal yang saya lewati; itu mengembalikan tinggi, rendah, dan curah hujan untuk Orlando. Setiap baris di sini mengarah ke halaman web yang berbeda. Bayangkan saja, jika bukan 15 baris tetapi 5.000 baris, Anda akan mengaturnya agar berjalan semalaman. Saya biasa menulis makro untuk ini. Faktanya, salah satu halaman web yang sedang dibahas adalah bagaimana membangun makro untuk mengikis halaman web dari ribuan halaman web yang berbeda di suatu situs, tidak perlu lagi dengan power query.ll klik kanan dan hapus kolom ini, klik kanan dan hapus kolom ini. Di sini, saya tidak membutuhkan waktu itu jadi saya akan mengatakan ini hanya kencan, oke. Dan lihat apa yang dilakukannya setiap tanggal yang saya lewati; itu mengembalikan tinggi, rendah, dan curah hujan untuk Orlando. Setiap baris di sini mengarah ke halaman web yang berbeda. Bayangkan saja, jika bukan 15 baris tetapi 5.000 baris, Anda akan mengaturnya agar berjalan semalaman. Saya biasa menulis makro untuk ini. Faktanya, salah satu halaman web yang sedang dibahas adalah bagaimana membangun makro untuk mengikis halaman web dari ribuan halaman web yang berbeda di suatu situs, tidak perlu lagi dengan power query.ll klik kanan dan hapus kolom ini, klik kanan dan hapus kolom ini. Di sini, saya tidak membutuhkan waktu itu jadi saya akan mengatakan ini hanya kencan, oke. Dan lihat apa yang dilakukannya setiap tanggal yang saya lewati; itu mengembalikan tinggi, rendah, dan curah hujan untuk Orlando. Setiap baris di sini mengarah ke halaman web yang berbeda. Bayangkan saja, jika bukan 15 baris tetapi 5.000 baris, Anda akan mengaturnya agar berjalan semalaman. Saya biasa menulis makro untuk ini. Faktanya, salah satu halaman web yang sedang dibahas adalah bagaimana membangun makro untuk mengikis halaman web dari ribuan halaman web yang berbeda di suatu situs, tidak perlu lagi dengan power query.s mengembalikan ketinggian, rendah, dan curah hujan untuk Orlando. Setiap baris di sini mengarah ke halaman web yang berbeda. Bayangkan saja, jika bukan 15 baris tetapi 5.000 baris, Anda akan mengaturnya agar berjalan semalaman. Saya biasa menulis makro untuk ini. Faktanya, salah satu halaman web yang sedang dibahas adalah bagaimana membangun makro untuk mengikis halaman web dari ribuan halaman web yang berbeda di suatu situs, tidak perlu lagi dengan power query.s mengembalikan ketinggian, rendah, dan curah hujan untuk Orlando. Setiap baris di sini mengarah ke halaman web yang berbeda. Bayangkan saja, jika bukan 15 baris tetapi 5.000 baris, Anda akan mengaturnya agar berjalan semalaman. Saya biasa menulis makro untuk ini. Faktanya, salah satu halaman web yang sedang dibahas adalah bagaimana membangun makro untuk mengikis halaman web dari ribuan halaman web yang berbeda di suatu situs, tidak perlu lagi dengan power query.
Now, when I Close & Load, it's funny the preview here is showing me all the results. When I Close & Load, they're actually going to go do each query. And so right now we have preview rows loaded and it will take a good long time for this information to get updated. So, go to lunch, do something especially if you're doing more than 15 rows. And it's funny the preview is correct but they're still going out and could chunk, could chunk, could chunking through each individual row.
And there it's loaded. Is this amazing or what? Hey, I do a lot of Excel seminars, the Power Excel seminar in Orlando, Florida. Look at these beautiful temperatures we have down here on November 4, 2016. My afternoon will be all about Power Query, Power BI, Power Pivot, Power Map. So, I'm going to invite you down to Orlando to check out this seminar. There'll be a link there in the top-right hand corner.
Alright, so recap. Today's trick is from this book, M is for (DATA) MONKEY. We built a query to get one web page and then edit that query to change it into a function. So right before the Let statement variable name => and then change the hard-coded URL to whatever that variable name is. Rename the query to fxWeather, Close & Load, the data disappears. Then, we use some sort of Excel trickery to create a table of all the URLs we want to crawl, create a query from that table. This has to be a Ctrl+T table, add a new column of Weather = fxWeather and again it has to match the case there, (URL), Expand that column, uncheck Prefix. BAM! It is amazing.
Thanks to Ken and Miguel for writing this book. Thanks to you for stopping by. Hope to see you in Orlando on November 4th 2016. See you next time for another netcast from.
Download File
Unduh file contoh di sini: Podcast2056.xlsm
