पावर क्वेरी बहुत शक्तिशाली है। लेकिन मैं पॉवर क्वेरी स्क्वॉयर करने वाला हूं … एक पृष्ठ के लिए एक क्वेरी लिखना और फिर एक्सेल वेब पृष्ठों की पूरी सूची के लिए एक ही क्वेरी करना।
वीडियो देखेंा
- आज की ट्रिक को M से अनुकूलित किया गया है जो डेटा मंकी बुक के लिए है
- एक वेब पेज से डेटा प्राप्त करने के लिए एक क्वेरी बनाएँ
- इसे (VariableName) => लेट से पहले फ़ंक्शन में बदलने के लिए क्वेरी को संपादित करें
- हार्ड-कोडित URL को VariableName में बदलें
- FxWeather के लिए क्वेरी का नाम बदलें
- बंद करें और लोड करें। डेटा गायब हो जाएगा।
- सभी URL की तालिका बनाने के लिए Excel Trickery का उपयोग करें
- उस तालिका से एक क्वेरी बनाएं।
- मौसम का एक नया कॉलम जोड़ें
=fxWeather((URL)) - स्तंभ का विस्तार करें। उपसर्ग को अनचेक करें
- गजब का!
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट से एक्सेल सीखें, एपिसोड 2056: पावर क्वेरी स्क्वॉयर
अरे, नेटकास्ट में वापस स्वागत है, मैं बिल जेलन हूं। जब मैं डलास में एक सम्मेलन में एक सेमिनार करने की तैयारी कर रहा था, तो मुझे यह चाल चली, जिसे एक्सेलप्लूजा कहा जाता है, जो अब तक का सबसे बड़ा एक्सेल सम्मेलन नाम है। आपको इसे डलास में हर सितंबर को देखना चाहिए।
और इसका श्रेय केन पल्स और मिगुएल एस्कोबार को दिया गया क्योंकि मुझे पावर क्वैरी पर एक घंटा करना था, इसलिए बेशक, मैंने पॉवर क्वेरी पर दुनिया की सबसे बड़ी किताब, कमाल की किताब निकाली। मैं पुस्तक के माध्यम से फ्लिप कर रहा था और मैंने देखा कि उनके पास एक तकनीक है और मैंने कहा, “एक सेकंड रुको। मैं यह देखने जा रहा हूं कि क्या मैं इस तकनीक को अपना सकता हूं। ” और यह मेरे लिए पावर क्वेरी में कमाल है। और यहाँ हम क्या करने जा रहे हैं, हम एक पावर क्वेरी लेने जा रहे हैं और फिर हम उस पावर क्वेरी, उस क्वेरी को दर्जनों बार, ठीक से चलाने जा रहे हैं?
और इसलिए, मैं जिस उदाहरण के साथ आया था वह वह था जहां मैं एक वेब पेज से डेटा खींचना चाहता था, ठीक है। और मैं बाहर चला गया - मैंने बस कुछ वेब पृष्ठों की तलाश की, जो मैं एक उदाहरण के रूप में उपयोग कर सकता हूं। मैं वेदर अंडरग्राउंड और यहां के URL पर समाप्त हुआ, और आप देख सकते हैं कि मैं डलास-फोर्ट वर्थ में था, इसलिए हम डलास के लिए डेटा खींच रहे हैं और यह 2 जनवरी, 2015 की तरह लग रहा है। तो वहीं URL में पैरामीटर हैं , सही? और यह एक URL है जो इस काम को करने के लिए सिर्फ पका हुआ है।
हम वेब पेज पर एक त्वरित नज़र डालेंगे, हालांकि यह इतना महत्वपूर्ण नहीं है। आप देखते हैं कि वेब पेज पर यहां बहुत सारे अलग-अलग डेटा हैं और मैंने तय किया कि मैं बस कोशिश कर रहा था और वर्षा और उच्च और निम्न तापमान प्राप्त कर रहा था। और यहाँ वह क्वेरी है। और मैं आपको यहीं बता दूं कि यह पॉडकास्ट इस बारे में नहीं है कि इस क्वेरी को कैसे बनाया जाए, नई क्वेरी, अन्य स्रोतों से, वेब से, URL निर्दिष्ट करें और फिर चरणों का एक गुच्छा जो मैं यहां प्राप्त करने के लिए विस्तार से नहीं जा रहा हूं मैक्स टेम्प, मिन टेम्प, और वर्षा का अंतिम उत्तर। मुद्दा यह है कि आप अपनी खुद की क्वेरी लेने जा रहे हैं और चीजों के एक समूह के लिए काम कर रहे हैं।
इसलिए, मैं क्लोज़ एंड लोड पर क्लिक करता हूं और यह क्वेरी काम कर रही है, यह मेरी एक पंक्ति लौटा रही है। सब कुछ बहुत ही अच्छा है। और मैं वापस आने वाला हूं, मैं इस क्वेरी को संपादित करने जा रहा हूं और मैं एडवांस्ड एडिटर को देखने जा रहा हूं। मैं इस क्वेरी को लेने जा रहा हूँ और मैं इसे एक फंक्शन बनाने जा रहा हूँ, ठीक है? तो यहाँ LET शब्द से पहले, मैं Enter दबाता हूँ। और कोष्ठक में, मैं इसे एक चर (MyURL) देने जा रहा हूं और फिर => थोड़ा तीर वहाँ, ठीक है? ठंडा। और फिर यहां नीचे, जहां उनके पास उद्धरणों में URL है, मैं उद्धरण सहित पूरे URL से छुटकारा पाना चाहता हूं और फिर मेरा परिवर्तनशील नाम MyURL टाइप करें, ठीक है। तो हम जो कह रहे हैं, हम उसे URL में पास करने जा रहे हैं और यह उसी क्वेरी को करने जा रहा है, लेकिन जो भी URL हम इसे पास करने के लिए करते हैं।
अब, यहाँ कुछ बातों का खंडन करते हुए, जब मैंने डन पर क्लिक किया, तो ओह लोग! मेरे सभी लागू चरण समाप्त हो गए हैं और वे चाहते हैं कि मैं एक पैरामीटर दर्ज करूं। बस इतना ही ध्यान न दें। हम इसका नाम बदलने जा रहे हैं; हम इसे fxWeather कहने जा रहे हैं। एफएक्स, निश्चित रूप से, फ़ंक्शन के लिए संक्षिप्त नाम होने के नाते और आपको वास्तव में इस नाम को याद रखना होगा और याद रखना होगा कि कौन से अक्षर बड़े हैं, यह कुछ मिनटों में बहुत महत्वपूर्ण होने वाला है। घर, बंद और लोड, और बम! सब कुछ ख़त्म हो गया। धत्तेरे की! पर यह ठीक है। ठीक है, तो हम जानते हैं कि यह वहाँ है। यह केवल एक कनेक्शन है। अब, मैं यहाँ आने वाला हूँ और यह सीधे पुराने एक्सेल है, ठीक है? तो यहाँ URL है, मैंने इसे URL के पहले भाग, URL के अंतिम भाग में तोड़ दिया। मुझे पता है कि मुझे तारीख लेने की आवश्यकता है; मुझे इसे वर्ष के इस अजीब प्रारूप में प्रारूपित करने की आवश्यकता है,महीने, और दिन इसलिए मैंने ऐसा करने के लिए पाठ फ़ंक्शन का उपयोग किया। शुरुआती तारीख में यहां डालें। मैं हवाई अड्डे को भी बदल सकता हूं, इसलिए अब मैं आगे-पीछे हूं। चलो ऑरलैंडो के लिए MCO करते हैं और चलो कुछ हालिया डेटा करते हैं। तो मैं 10/1/2016 से शुरू करूंगा, ठीक है। तो अब हमारे पास यहाँ पर इस महान छोटी मेज की स्थापना है। और वैसे, यह एक मेज होना चाहिए। आपको तालिका या Ctrl + T के रूप में प्रारूप का उपयोग करना होगा। तो, आप जानते हैं, यह सिर्फ उस तारीख को पकड़ रहा है और फिर + 1 + 1 + 1। मैं इसे प्रारूपित करता हूं, मैं URL का निर्माण करता हूं।आपको तालिका या Ctrl + T के रूप में प्रारूप का उपयोग करना होगा। तो, आप जानते हैं, यह सिर्फ उस तारीख को हथिया रहा है और फिर + 1 + 1 + 1। मैं इसे प्रारूपित करता हूं, मैं URL का निर्माण करता हूं।आपको तालिका या Ctrl + T के रूप में प्रारूप का उपयोग करना होगा। तो, आप जानते हैं, यह सिर्फ उस तारीख को हथिया रहा है और फिर + 1 + 1 + 1। मैं इसे प्रारूपित करता हूं, मैं URL का निर्माण करता हूं।
अब ठीक है, हम इस तालिका से एक क्वेरी बनाने जा रहे हैं। ठीक है, और मेरी जानकारी है। मैं एक नया कॉलम जोड़ने जा रहा हूं, एक कस्टम कॉलम जोड़ें, कॉलम को वेदर कहा जा रहा है, और सूत्र = fxWeather हो रहा है। सुनिश्चित करें कि यह बिल्कुल वैसा ही मामला है, समान ऊपरी और निचले मामले के अक्षर हैं, और हम कोष्ठक को बंद करते हुए उस क्षेत्र को URL की तरह सम्मिलित करेंगे। कोई सिंटैक्स त्रुटि नहीं, ठीक क्लिक करें। वे यहां गोपनीयता के बारे में जानना चाहते हैं, यह सब सार्वजनिक डेटा है, सहेजें पर क्लिक करें, ठीक है। तो, हमारी तारीख है। यह मज़ेदार है कि उन्होंने वास्तव में मेरे प्रारूप को किसी ऐसी चीज़ में बदल दिया है जो उस तरह से नहीं दिखती है जैसा मैंने शुरू किया था। और फिर यहाँ विस्तार प्रतीक के साथ मौसम है। तो मैं विस्तार प्रतीक पर क्लिक करने जा रहा हूँ, उपसर्ग के रूप में मूल कॉलम नाम का उपयोग अनचेक करें। मुझे अधिकतम, न्यूनतम, वर्षा चाहिए,ओके पर क्लिक करें। ठीक है, और अब मुझे जो भी चाहिए वह है तारीख और वह जानकारी। तो मैं इस कॉलम को राइट-क्लिक करूँगा और राइट-क्लिक करूँगा और इस कॉलम को हटा दूँगा। यहाँ पर, मुझे उस समय की आवश्यकता नहीं है इसलिए मैं कहूंगा कि यह सिर्फ एक तारीख है, ठीक है। और देखो कि यह हर तारीख क्या कर रहा है कि मैं इसे पारित कर रहा हूं; यह उच्च, निम्न और ओरलैंडो के लिए वर्षा लौटा रहा है। यहाँ हर पंक्ति एक अलग वेब पेज पर जा रही है। जरा सोचिए, अगर यह 15 पंक्तियां नहीं बल्कि 5,000 पंक्तियां थीं, तो आप इसे रात भर चलाने के लिए सेट कर देंगे। मैं इसके लिए मैक्रोज़ लिखता था। वास्तव में, वेब पेजों में से एक यह है कि किसी साइट पर एक हजार अलग-अलग वेब पेजों से वेब पेजों को खुरचने के लिए मैक्रो का निर्माण कैसे किया जाए, जो कि पावर क्वेरी के साथ अब आवश्यक नहीं है।राइट-क्लिक करें और इस कॉलम को निकालें, राइट-क्लिक करें और इस कॉलम को निकालें। यहाँ पर, मुझे उस समय की आवश्यकता नहीं है इसलिए मैं कहूंगा कि यह सिर्फ एक तारीख है, ठीक है। और देखो कि यह हर तारीख क्या कर रहा है कि मैं इसे पारित कर रहा हूं; यह उच्च, निम्न और ओरलैंडो के लिए वर्षा लौटा रहा है। यहाँ हर पंक्ति एक अलग वेब पेज पर जा रही है। जरा सोचिए, अगर यह 15 पंक्तियां नहीं बल्कि 5,000 पंक्तियां थीं, तो आप इसे रात भर चलाने के लिए सेट कर देंगे। मैं इसके लिए मैक्रोज़ लिखता था। वास्तव में, वेब पेजों में से एक यह है कि किसी साइट पर एक हजार अलग-अलग वेब पेजों से वेब पेजों को खुरचने के लिए मैक्रो का निर्माण कैसे किया जाए, जो कि पावर क्वेरी के साथ अब आवश्यक नहीं है।राइट-क्लिक करें और इस कॉलम को निकालें, राइट-क्लिक करें और इस कॉलम को निकालें। यहाँ पर, मुझे उस समय की आवश्यकता नहीं है इसलिए मैं कहूंगा कि यह सिर्फ एक तारीख है, ठीक है। और देखो कि यह हर तारीख क्या कर रहा है कि मैं इसे पारित कर रहा हूं; यह उच्च, निम्न और ऑरलैंडो के लिए वर्षा लौटा रहा है। यहाँ हर पंक्ति एक अलग वेब पेज पर जा रही है। जरा सोचिए, अगर यह 15 पंक्तियां नहीं बल्कि 5,000 पंक्तियां थीं, तो आप इसे रात भर चलाने के लिए सेट कर देंगे। मैं इसके लिए मैक्रोज़ लिखता था। वास्तव में, वेब पेजों में से एक यह है कि किसी साइट पर एक हजार अलग-अलग वेब पेजों से वेब पेजों को खुरचने के लिए मैक्रो का निर्माण कैसे किया जाए, पावर क्वेरी के साथ अब और आवश्यक नहीं है।ओरलैंडो के लिए उच्च, निम्न और वर्षा लौटाता है। यहाँ हर पंक्ति एक अलग वेब पेज पर जा रही है। जरा सोचिए, अगर यह 15 पंक्तियां नहीं बल्कि 5,000 पंक्तियां थीं, तो आप इसे रात भर चलाने के लिए सेट कर देंगे। मैं इसके लिए मैक्रोज़ लिखता था। वास्तव में, वेब पेजों में से एक यह है कि किसी साइट पर एक हजार अलग-अलग वेब पेजों से वेब पेजों को खुरचने के लिए मैक्रो का निर्माण कैसे किया जाए, पावर क्वेरी के साथ अब और आवश्यक नहीं है।ओरलैंडो के लिए उच्च, निम्न और वर्षा लौटाता है। यहां हर पंक्ति एक अलग वेब पेज पर जा रही है। जरा सोचिए, अगर यह 15 पंक्तियां नहीं बल्कि 5,000 पंक्तियां थीं, तो आप इसे रात भर चलाने के लिए सेट कर देंगे। मैं इसके लिए मैक्रोज़ लिखता था। वास्तव में, वेब पेजों में से एक यह है कि किसी साइट पर एक हजार अलग-अलग वेब पेजों से वेब पेजों को खुरचने के लिए मैक्रो का निर्माण कैसे किया जाए, पावर क्वेरी के साथ अब और आवश्यक नहीं है।
अब, जब मैंने बंद किया और लोड किया, तो यह हास्यास्पद है कि यहां पूर्वावलोकन मुझे सभी परिणाम दिखा रहा है। जब मैं बंद करता हूं और लोड करता हूं, तो वे वास्तव में प्रत्येक क्वेरी करने जाते हैं। और इसलिए अभी हमारे पास पूर्वावलोकन पंक्तियाँ भरी हुई हैं और इस जानकारी को अद्यतन होने में अच्छा लंबा समय लगेगा। इसलिए, दोपहर के भोजन पर जाएं, विशेष रूप से कुछ करें यदि आप 15 से अधिक पंक्तियाँ कर रहे हैं। और यह हास्यास्पद है कि पूर्वावलोकन सही है, लेकिन वे अभी भी बाहर जा रहे हैं और प्रत्येक व्यक्ति पंक्ति के माध्यम से चंक कर सकते हैं, चंक कर सकते हैं।
और वहाँ यह भरी हुई है। यह अद्भुत है या क्या? अरे, मैं बहुत सारे एक्सेल सेमिनार करता हूं, ऑरलैंडो, फ्लोरिडा में पावर एक्सेल सेमिनार। इन सुंदर तापमानों को देखें, जिन्हें हमने 4 नवंबर, 2016 को यहां देखा था। मेरी दोपहर पावर क्वेरी, पावर बीआई, पावर पिवट, पावर मैप के बारे में होगी। इसलिए, मैं आपको इस संगोष्ठी की जाँच के लिए ऑरलैंडो में आमंत्रित करने जा रहा हूँ। शीर्ष-दाएँ हाथ के कोने में एक लिंक होगा।
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
यहाँ नमूना फ़ाइल डाउनलोड करें: Podcast2056.xlsm
