Power Query khá mạnh mẽ. Nhưng tôi sắp thực hiện Power Query Squared… viết một truy vấn cho một trang và sau đó để Excel thực hiện cùng một truy vấn cho toàn bộ danh sách các trang web.
Xem video
- Thủ thuật hôm nay được phỏng theo sách M is for Data Monkey
- Tạo truy vấn để lấy dữ liệu từ một trang web
- Chỉnh sửa truy vấn để thay đổi nó thành một hàm với (VariableName) => trước Let
- Thay đổi URL được mã hóa cứng thành VariableName
- Đổi tên truy vấn thành fxWeather
- Đóng và tải. Dữ liệu sẽ biến mất.
- Sử dụng Thủ thuật Excel để tạo một bảng gồm tất cả các URL
- Tạo một truy vấn từ bảng đó.
- Thêm một cột Thời tiết mới
=fxWeather((URL)) - Mở rộng cột. Bỏ chọn Tiền tố
- Kinh ngạc!
Bản ghi video
Học Excel từ Podcast, Episode 2056: Power Query Squared
Chào mừng bạn trở lại netcast, tôi là Bill Jelen. Tôi bắt gặp thủ thuật này khi đang chuẩn bị tham gia hội thảo tại một hội nghị ở Dallas có tên là Excelapalooza, tên hội nghị Excel vĩ đại nhất từ trước đến nay. Bạn nên xem nó vào tháng 9 hàng năm ở Dallas.
Và ghi công cho Ken Puls và Miguel Escobar vì tôi đã phải thực hiện một giờ về truy vấn nguồn, vì vậy, tất nhiên, tôi đã lấy ra cuốn sách tuyệt vời, cuốn sách hay nhất thế giới về Power Query. Tôi đang lật giở cuốn sách và tôi thấy họ có một kỹ thuật và tôi nói, “Chờ một chút. Tôi sẽ xem liệu tôi có thể thích ứng với kỹ thuật này hay không. ” Và điều này đối với tôi là tuyệt vời trong truy vấn nguồn. Và đây là những gì chúng ta sẽ làm, chúng ta sẽ thực hiện một truy vấn nguồn và sau đó chúng ta sẽ chạy truy vấn nguồn đó, truy vấn đó, hàng chục lần, được chứ?
Và vì vậy, ví dụ mà tôi nghĩ ra là nơi tôi muốn lấy dữ liệu từ một trang web. Và tôi đi ra ngoài - tôi chỉ tìm kiếm một số trang web mà tôi có thể - mà tôi có thể sử dụng làm ví dụ. Tôi đã kết thúc ở Weather Underground và đây là URL, và bạn có thể thấy rằng tôi đã ở Dallas-Fort Worth, vì vậy chúng tôi đang lấy dữ liệu cho Dallas và có vẻ như là ngày 2 tháng 1 năm 2015. Vì vậy, ngay trong URL là các thông số , đúng? Và đó là một URL vừa đủ để làm cho điều này hoạt động.
Chúng ta sẽ xem qua trang web mặc dù nó không quá quan trọng. Bạn thấy có rất nhiều dữ liệu khác nhau ở đây trên trang web và tôi quyết định rằng mình sẽ thử lấy lượng mưa và nhiệt độ cao và thấp. Và đây là truy vấn. Và hãy để tôi nói với bạn ngay tại đây rằng podcast này không phải về cách Tạo Truy vấn này, Truy vấn mới, Từ các nguồn khác, Từ web, chỉ định URL và sau đó là một loạt các bước mà tôi sẽ không trình bày chi tiết ở đây để có được câu trả lời cuối cùng của Nhiệt độ tối đa, Nhiệt độ tối thiểu và lượng mưa. Vấn đề là bạn sẽ thực hiện truy vấn của riêng mình và làm cho nó hoạt động cho nhiều thứ.
Vì vậy, tôi nhấp vào Đóng & Tải và truy vấn này đang hoạt động, nó trả về một hàng của tôi. Mọi thứ đều tuyệt vời. Và tôi sẽ quay lại, tôi sẽ Chỉnh sửa truy vấn này và tôi sẽ đi tới Chế độ xem, Trình chỉnh sửa nâng cao. Tôi sẽ thực hiện truy vấn này và tôi sẽ biến nó thành một hàm, được chứ? Vì vậy, ngay tại đây trước từ LET, tôi nhấn Enter. Và trong dấu ngoặc đơn, tôi sẽ cung cấp cho nó một biến (MyURL) và sau đó => mũi tên nhỏ ở đó, được chứ? Mát mẻ. Và dưới đây, nơi họ có URL trong dấu ngoặc kép, tôi muốn loại bỏ toàn bộ URL bao gồm cả dấu ngoặc kép và sau đó nhập tên biến MyURL của tôi, được rồi. Vì vậy, những gì chúng tôi đang nói là, chúng tôi sẽ chuyển nó đến URL và nó sẽ thực hiện cùng một truy vấn nhưng với bất kỳ URL nào chúng tôi tình cờ chuyển nó.
Bây giờ, một vài điều đáng kinh ngạc ở đây, khi tôi nhấp vào Xong, ôi các bạn! Tất cả các bước áp dụng của tôi đã biến mất và họ muốn tôi nhập một tham số. Chỉ cần bỏ qua tất cả những điều đó. Chúng tôi sẽ đổi tên cái này; chúng tôi sẽ gọi nó là fxWeather. FX, tất nhiên, là chữ viết tắt của hàm và bạn phải thực sự nhớ tên này và nhớ những chữ cái nào được viết hoa, điều đó sẽ rất quan trọng trong vài phút. Home, Close & Load, và BAM! Mọi thứ đã biến mất. Ôi không! Nhưng nó ổn. Được rồi, vì vậy chúng tôi biết nó ở đó. Nó chỉ là một kết nối. Bây giờ, tôi sẽ quay lại đây và đây chỉ là một Excel cũ, được chứ? Vì vậy, đây là URL, tôi đã chia nó thành phần đầu tiên của URL, phần cuối của URL. Tôi biết rằng tôi cần phải xác định ngày tháng; Tôi cần định dạng nó theo định dạng kỳ lạ này của năm,tháng và ngày nên tôi đã sử dụng hàm TEXT để làm điều đó. Đặt ngày bắt đầu ở đây. Tôi thậm chí có thể thay đổi sân bay, vì vậy bây giờ tôi đang quay lại. Hãy làm MCO cho Orlando và chúng ta hãy làm một số dữ liệu gần đây. Vì vậy, tôi sẽ bắt đầu vào 10/1/2016, được thôi. Vì vậy, bây giờ chúng tôi có chiếc bàn nhỏ tuyệt vời này được thiết lập ở đây. Và nhân tiện, nó phải là một cái bàn. Bạn phải sử dụng định dạng dưới dạng bảng hoặc Ctrl + T. Vì vậy, bạn biết đấy, đây chỉ là nắm bắt ngày đó và sau đó + 1 + 1 + 1. Tôi định dạng nó, tôi xây dựng URL.Bạn phải sử dụng định dạng dưới dạng bảng hoặc Ctrl + T. Vì vậy, bạn biết đấy, đây chỉ là nắm bắt ngày đó và sau đó + 1 + 1 + 1. Tôi định dạng nó, tôi xây dựng URL.Bạn phải sử dụng định dạng dưới dạng bảng hoặc Ctrl + T. Vì vậy, bạn biết đấy, đây chỉ là nắm bắt ngày đó và sau đó + 1 + 1 + 1. Tôi định dạng nó, tôi xây dựng URL.
Được rồi, chúng ta sẽ tạo một truy vấn từ bảng này. Được rồi, và có thông tin của tôi. Tôi sẽ thêm một cột mới, thêm một cột tùy chỉnh, các cột sẽ được gọi là Thời tiết và công thức sẽ là = fxWeather. Đảm bảo rằng nó giống hệt nhau, cùng một chữ hoa và chữ thường và chúng tôi sẽ chèn trường có tên URL như vậy, đóng ngoặc đơn. Không có lỗi cú pháp, bấm OK. Họ muốn biết về Quyền riêng tư ở đây, đây là toàn bộ dữ liệu Công khai, bấm Lưu là được. Vì vậy, có ngày của chúng tôi. Thật buồn cười là họ đã thực sự thay đổi định dạng của tôi thành một thứ không giống như những gì tôi đã bắt đầu. Và đây là Thời tiết với biểu tượng mở rộng. Vì vậy, tôi sẽ nhấp vào biểu tượng mở rộng, bỏ chọn Sử dụng tên cột gốc làm tiền tố. Tôi muốn Max, Min, Precipitation,bấm OK. Được rồi, và bây giờ tất cả những gì tôi cần là ngày tháng và thông tin đó. Vì vậy, tôi sẽ nhấp chuột phải và xóa cột này, nhấp chuột phải và xóa cột này. Đằng này, tôi không cần thời gian nên tôi sẽ nói đây chỉ là một Buổi hẹn hò, được thôi. Và xem nó đang làm gì mỗi ngày tôi đi qua nó; nó trả lại mức cao, mức thấp và lượng mưa cho Orlando. Mỗi hàng ở đây sẽ đi ra một trang web khác nhau. Chỉ cần tưởng tượng, nếu nó không phải là 15 hàng mà là 5.000 hàng, bạn sẽ thiết lập nó để chạy qua đêm. Tôi đã từng viết macro cho việc này. Trên thực tế, một trong những trang web là cách xây dựng macro để loại bỏ các trang web từ hàng nghìn trang web khác nhau tại một trang web, không cần thiết nữa với truy vấn nguồn.Nhấp chuột phải và xóa cột này, nhấp chuột phải và xóa cột này. Đằng này, tôi không cần thời gian đó nên tôi sẽ nói đây chỉ là một Buổi hẹn hò, được thôi. Và xem nó đang làm gì mỗi ngày tôi đi qua nó; nó trả lại mức cao, mức thấp và lượng mưa cho Orlando. Mỗi hàng ở đây sẽ đi ra một trang web khác nhau. Chỉ cần tưởng tượng, nếu nó không phải là 15 hàng mà là 5.000 hàng, bạn sẽ thiết lập nó để chạy qua đêm. Tôi đã từng viết macro cho việc này. Trên thực tế, một trong những trang web là cách xây dựng macro để loại bỏ các trang web từ hàng nghìn trang web khác nhau tại một trang web, không cần thiết nữa với truy vấn nguồn.Nhấp chuột phải và xóa cột này, nhấp chuột phải và xóa cột này. Đằng này, tôi không cần thời gian đó nên tôi sẽ nói đây chỉ là một Buổi hẹn hò, được thôi. Và xem nó đang làm gì mỗi ngày tôi đi qua nó; nó trả lại mức cao, mức thấp và lượng mưa cho Orlando. Mỗi hàng ở đây sẽ đi ra một trang web khác nhau. Chỉ cần tưởng tượng, nếu nó không phải là 15 hàng mà là 5.000 hàng, bạn sẽ thiết lập nó để chạy qua đêm. Tôi đã từng viết macro cho việc này. Trên thực tế, một trong những trang web là cách xây dựng macro để loại bỏ các trang web từ hàng nghìn trang web khác nhau tại một trang web, không cần thiết nữa với truy vấn nguồn.trả lại mức cao, mức thấp và lượng mưa cho Orlando. Mỗi hàng ở đây sẽ đi ra một trang web khác nhau. Chỉ cần tưởng tượng, nếu nó không phải là 15 hàng mà là 5.000 hàng, bạn sẽ thiết lập nó để chạy qua đêm. Tôi đã từng viết macro cho việc này. Trên thực tế, một trong những trang web là cách xây dựng macro để loại bỏ các trang web từ hàng nghìn trang web khác nhau tại một trang web, không cần thiết nữa với truy vấn nguồn.trả lại mức cao, mức thấp và lượng mưa cho Orlando. Mỗi hàng ở đây sẽ đi ra một trang web khác nhau. Chỉ cần tưởng tượng, nếu nó không phải là 15 hàng mà là 5.000 hàng, bạn sẽ thiết lập nó để chạy qua đêm. Tôi đã từng viết macro cho việc này. Trên thực tế, một trong những trang web là cách xây dựng macro để loại bỏ các trang web từ hàng nghìn trang web khác nhau tại một trang web, không cần thiết nữa với truy vấn nguồn.
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
Tải file mẫu tại đây: Podcast2056.xlsm
