पॉवर क्वेरीमध्‍ये डेटा पाथचे पॅरामीटरायझिंग

तुम्ही मायक्रोसॉफ्ट एक्सेलमध्ये मोफत पॉवर क्वेरी अॅड-इनची साधने वापरण्यास आधीच सुरुवात केली असेल, तर लवकरच तुम्हाला एक अत्यंत विशेष, परंतु सतत स्त्रोत डेटाच्या लिंक तोडण्याशी संबंधित अत्यंत त्रासदायक आणि त्रासदायक समस्येचा सामना करावा लागेल. समस्येचे सार हे आहे की जर तुमच्या क्वेरीमध्ये तुम्ही बाह्य फाइल्स किंवा फोल्डर्सचा संदर्भ घेत असाल, तर पॉवर क्वेरी हार्डकोड त्यांना क्वेरी मजकूरात परिपूर्ण मार्ग देते. आपल्या संगणकावर सर्व काही ठीक आहे, परंतु आपण आपल्या सहकार्यांना विनंतीसह फाइल पाठविण्याचा निर्णय घेतल्यास, ते निराश होतील, कारण. त्यांच्या संगणकावरील स्त्रोत डेटासाठी त्यांचा मार्ग वेगळा आहे आणि आमची क्वेरी कार्य करणार नाही.

अशा परिस्थितीत काय करावे? पुढील उदाहरणासह या प्रकरणाकडे अधिक तपशीलवार पाहू.

समस्येचे सूत्रीकरण

समजा फोल्डरमध्ये आहे E:विक्री अहवाल फाइल पडून आहे शीर्ष 100 products.xls, जे आमच्या कॉर्पोरेट डेटाबेस किंवा ERP सिस्टीम (1C, SAP, इ.) वरून अपलोड केलेले आहे. या फाईलमध्ये सर्वात लोकप्रिय कमोडिटी आयटमची माहिती आहे आणि ती आत यासारखी दिसते:

एक्सेलमध्ये या फॉर्ममध्ये त्याच्यासोबत काम करणे जवळजवळ अशक्य आहे हे कदाचित बॅटपासूनच स्पष्ट आहे: डेटा, विलीन केलेले सेल, अतिरिक्त कॉलम्स, एक मल्टी-लेव्हल हेडर, इत्यादीसह रिक्त पंक्ती व्यत्यय आणतील.

म्हणून, त्याच फोल्डरमध्ये या फाईलच्या पुढे, आम्ही दुसरी नवीन फाइल तयार करतो Handler.xlsx, ज्यामध्ये आम्ही एक पॉवर क्वेरी क्वेरी तयार करू जी स्रोत अपलोड फाइलमधून कुरूप डेटा लोड करेल शीर्ष 100 products.xls, आणि त्यांना क्रमाने ठेवा:

बाह्य फाइलला विनंती करत आहे

फाइल उघडत आहे Handler.xlsx, टॅबवर निवडा डेटा आदेश डेटा मिळवा - फाइलमधून - एक्सेल वर्कबुकमधून (डेटा — डेटा मिळवा — फाइलमधून — एक्सेलमधून), नंतर स्त्रोत फाइलचे स्थान आणि आम्हाला आवश्यक शीट निर्दिष्ट करा. निवडलेला डेटा पॉवर क्वेरी एडिटरमध्ये लोड केला जाईल:

चला त्यांना सामान्य स्थितीत आणूया:

1. सह रिक्त ओळी हटवा मुख्यपृष्ठ — ओळी हटवा — रिक्त ओळी हटवा (मुख्यपृष्ठ — पंक्ती काढा — रिक्त पंक्ती काढा).
2. द्वारे अनावश्यक शीर्ष 4 ओळी हटवा मुख्यपृष्ठ — पंक्ती हटवा — शीर्ष पंक्ती हटवा (मुख्यपृष्ठ — पंक्ती काढा — वरच्या पंक्ती काढा).
3. बटणासह टेबल शीर्षलेखावर पहिली पंक्ती वाढवा शीर्षलेख म्हणून पहिली ओळ वापरा टॅब होम पेज (मुख्यपृष्ठ - शीर्षलेख म्हणून पहिली पंक्ती वापरा).
4. कमांड वापरून दुसऱ्या स्तंभातील उत्पादनाच्या नावापासून पाच अंकी लेख वेगळे करा विभाजित स्तंभ टॅब परिवर्तन (ट्रान्सफॉर्म — स्प्लिट कॉलम).
5. अनावश्यक स्तंभ हटवा आणि चांगल्या दृश्यमानतेसाठी उर्वरित शीर्षलेखांचे नाव बदला.

परिणामी, आम्हाला खालील, अधिक आनंददायी चित्र मिळाले पाहिजे:

आमच्या फाईलमधील शीटवर हे एननोबल केलेले टेबल परत अपलोड करणे बाकी आहे Handler.xlsx संघ बंद करा आणि डाउनलोड करा (घर - बंद करा आणि लोड करा) टॅब होम पेज:

विनंतीमध्ये फाइलचा मार्ग शोधत आहे

आता आपली क्वेरी “अंडर द हूड” कशी दिसते ते पाहू या, पॉवर क्वेरीमध्ये “M” या संक्षिप्त नावाने तयार केलेल्या अंतर्गत भाषेत. हे करण्यासाठी, उजव्या उपखंडात त्यावर डबल क्लिक करून आमच्या क्वेरीवर परत जा विनंत्या आणि कनेक्शन आणि टॅबवर पुनरावलोकन निवडा प्रगत संपादक (पहा — प्रगत संपादक):

उघडणाऱ्या विंडोमध्ये, दुसरी ओळ लगेच आमच्या मूळ अपलोड फाइलसाठी हार्ड-कोड केलेला मार्ग प्रकट करते. जर आपण या मजकूर स्ट्रिंगला पॅरामीटर, व्हेरिएबल किंवा एक्सेल शीट सेलच्या लिंकसह बदलू शकलो जिथे हा मार्ग आधीच लिहिलेला आहे, तर आपण नंतर तो सहजपणे बदलू शकतो.

फाईल पाथसह एक स्मार्ट टेबल जोडा

आता पॉवर क्वेरी बंद करू आणि आमच्या फाईलवर परत या Handler.xlsx. चला एक नवीन रिकामी शीट जोडू आणि त्यावर एक लहान "स्मार्ट" टेबल बनवू, ज्याच्या एकमेव सेलमध्ये आमच्या स्त्रोत डेटा फाइलचा संपूर्ण मार्ग लिहिला जाईल:

नियमित श्रेणीतून स्मार्ट टेबल तयार करण्यासाठी, तुम्ही कीबोर्ड शॉर्टकट वापरू शकता Ctrl+T किंवा बटण सारणी म्हणून स्वरूपित करा टॅब होम पेज (मुख्यपृष्ठ - सारणी म्हणून स्वरूपित). स्तंभ शीर्षक (सेल A1) पूर्णपणे काहीही असू शकते. हे देखील लक्षात घ्या की स्पष्टतेसाठी मी टेबलला एक नाव दिले आहे घटके टॅब रचनाकार (डिझाइन).

एक्सप्लोररवरून मार्ग कॉपी करणे किंवा मॅन्युअली एंटर करणे अर्थातच विशेषतः कठीण नाही, परंतु मानवी घटक कमी करणे आणि शक्य असल्यास मार्ग स्वयंचलितपणे निर्धारित करणे चांगले आहे. हे मानक एक्सेल वर्कशीट फंक्शन वापरून लागू केले जाऊ शकते सेल (सेल), जे वितर्क म्हणून निर्दिष्ट केलेल्या सेलबद्दल उपयुक्त माहितीचा एक समूह देऊ शकते – वर्तमान फाइलच्या मार्गासह:

जर आपण असे गृहीत धरले की स्त्रोत डेटा फाइल नेहमी आपल्या प्रोसेसरच्या फोल्डरमध्ये असते, तर आपल्याला आवश्यक असलेला मार्ग खालील सूत्राद्वारे तयार केला जाऊ शकतो:

=LEFT(CELL("फाइलनाव");FIND("[";CELL("फाइलनाव"))-1)&"Top 100 products.xls"

किंवा इंग्रजी आवृत्तीमध्ये:

=LEFT(CELL(«फाइलनाव»);FIND(«[«;CELL(«फाइलनाव»))-1)&»Топ-100 товаров.xls»

… कार्य कुठे आहे LEVSIMV (डावे) पूर्ण दुव्यापासून सुरुवातीच्या चौरस ब्रॅकेटपर्यंत मजकूराचा एक तुकडा घेते (म्हणजे वर्तमान फोल्डरचा मार्ग), आणि नंतर आमच्या स्त्रोत डेटा फाइलचे नाव आणि विस्तार त्यावर चिकटवले जाते.

क्वेरीमधील पथ पॅरामीटराइज करा

शेवटचा आणि सर्वात महत्त्वाचा टच बाकी आहे - विनंतीमध्ये स्त्रोत फाइलचा मार्ग लिहिण्यासाठी शीर्ष 100 products.xls, आमच्या तयार केलेल्या "स्मार्ट" टेबलच्या सेल A2 चा संदर्भ देत आहे घटके.

हे करण्यासाठी, पॉवर क्वेरी क्वेरीवर परत जाऊ आणि ते पुन्हा उघडू प्रगत संपादक टॅब पुनरावलोकन (पहा — प्रगत संपादक). कोट्समध्ये मजकूर स्ट्रिंग-पाथऐवजी "ई:विक्री अहवाल टॉप 100 उत्पादने.xlsx" चला खालील रचना सादर करूया:

Excel.CurrentWorkbook(){[नाव=”सेटिंग्ज”][सामग्री]२,३,४ {}[स्रोत डेटाचा मार्ग]

चला त्यात काय समाविष्ट आहे ते पाहूया:

• Excel.CurrentWorkbook() सध्याच्या फाईलमधील मजकुरात प्रवेश करण्यासाठी M भाषेचे कार्य आहे
• {[नाव=”सेटिंग्ज”][सामग्री] - हे मागील फंक्शनचे परिष्करण पॅरामीटर आहे, जे सूचित करते की आम्हाला "स्मार्ट" सारणीची सामग्री मिळवायची आहे घटके
• [स्रोत डेटाचा मार्ग] टेबलमधील स्तंभाचे नाव आहे घटकेज्याचा आम्ही संदर्भ देतो
  
• २,३,४ {} टेबलमधील पंक्ती क्रमांक आहे घटकेज्यातून आम्हाला डेटा घ्यायचा आहे. कॅप मोजत नाही आणि क्रमांकन शून्यापासून सुरू होते, एकापासून नाही.

खरं तर, हे सर्व आहे.

त्यावर क्लिक करणे बाकी आहे समाप्त आणि आमची विनंती कशी कार्य करते ते तपासा. आता, दोन्ही फायलींसह संपूर्ण फोल्डर दुसर्‍या PC वर पाठवताना, विनंती कार्यान्वित राहील आणि डेटाचा मार्ग स्वयंचलितपणे निर्धारित करेल.

• पॉवर क्वेरी म्हणजे काय आणि मायक्रोसॉफ्ट एक्सेलमध्ये काम करताना त्याची आवश्यकता का आहे
• Power Query मध्ये फ्लोटिंग टेक्स्ट स्निपेट कसे इंपोर्ट करायचे
• पॉवर क्वेरीसह फ्लॅट टेबलवर XNUMXD क्रॉसटॅब पुन्हा डिझाइन करणे