सामग्रीहरू
यदि तपाईंले Microsoft Excel मा नि:शुल्क पावर क्वेरी एड-इनका उपकरणहरू प्रयोग गर्न सुरु गरिसक्नुभएको छ भने, चाँडै नै तपाईंले स्रोत डेटामा लगातार लिंकहरू तोड्नेसँग सम्बन्धित एक उच्च विशिष्ट, तर धेरै बारम्बार र कष्टप्रद समस्याको सामना गर्नुहुनेछ। समस्याको सार यो हो कि यदि तपाइँको क्वेरीमा तपाइँ बाह्य फाइलहरू वा फोल्डरहरू सन्दर्भ गर्नुहुन्छ भने, त्यसपछि Power Query हार्डकोडले तिनीहरूलाई क्वेरी पाठमा पूर्ण मार्ग दिन्छ। तपाईको कम्प्युटरमा सबै कुरा राम्रोसँग काम गर्दछ, तर यदि तपाईले आफ्ना सहकर्मीहरूलाई अनुरोधको साथ फाइल पठाउने निर्णय गर्नुभयो भने, तिनीहरू निराश हुनेछन्, किनभने। तिनीहरूको कम्प्युटरमा स्रोत डेटाको लागि फरक बाटो छ, र हाम्रो क्वेरीले काम गर्दैन।
यस्तो अवस्थामा के गर्ने ? निम्न उदाहरणको साथ यस मामलालाई थप विस्तारमा हेरौं।
समस्याको सूत्रीकरण
मानौं हामीसँग फोल्डरमा छ E:बिक्री रिपोर्टहरू फाइल छ शीर्ष 100 products.xls, जुन हाम्रो कर्पोरेट डाटाबेस वा ERP प्रणाली (1C, SAP, आदि) बाट अपलोड गरिएको हो।
यो एक्सेलमा यो फारममा काम गर्न लगभग असम्भव छ भन्ने कुरा ब्याटबाटै स्पष्ट छ: डाटा, मर्ज गरिएका कक्षहरू, अतिरिक्त स्तम्भहरू, बहु-स्तर हेडर, इत्यादिको साथमा खाली पङ्क्तिहरू।
त्यसकारण, एउटै फोल्डरमा यो फाइलको छेउमा, हामी अर्को नयाँ फाइल सिर्जना गर्छौं Handler.xlsx, जसमा हामी एक पावर क्वेरी क्वेरी सिर्जना गर्नेछौं जसले स्रोत अपलोड फाइलबाट बदसूरत डाटा लोड गर्नेछ। शीर्ष 100 products.xls, र तिनीहरूलाई क्रमबद्ध राख्नुहोस्:
बाह्य फाइलमा अनुरोध गर्दै
फाइल खोल्दै Handler.xlsx, ट्याबमा चयन गर्नुहोस् तथ्याङ्क आदेश डाटा प्राप्त गर्नुहोस् - फाइलबाट - एक्सेल कार्यपुस्तिकाबाट (डेटा - डाटा प्राप्त गर्नुहोस् - फाइलबाट - एक्सेलबाट), त्यसपछि स्रोत फाइलको स्थान र हामीलाई चाहिने पाना निर्दिष्ट गर्नुहोस्। चयन गरिएको डाटा पावर क्वेरी सम्पादकमा लोड हुनेछ:
तिनीहरूलाई सामान्यमा फिर्ता ल्याउनुहोस्:
- खाली रेखाहरू मेटाउनुहोस् गृह - रेखाहरू मेटाउनुहोस् - खाली रेखाहरू मेटाउनुहोस् (घर - पङ्क्तिहरू हटाउनुहोस् - खाली पङ्क्तिहरू हटाउनुहोस्).
- अनावश्यक शीर्ष 4 रेखाहरू मार्फत मेटाउनुहोस् गृह - पङ्क्तिहरू मेटाउनुहोस् - शीर्ष पङ्क्तिहरू मेटाउनुहोस् (घर - पङ्क्तिहरू हटाउनुहोस् - शीर्ष पङ्क्तिहरू हटाउनुहोस्).
- बटनको साथ तालिका हेडरमा पहिलो पङ्क्ति उठाउनुहोस् हेडरको रूपमा पहिलो लाइन प्रयोग गर्नुहोस् ट्याब गृहपृष्ठ (घर - हेडरको रूपमा पहिलो पङ्क्ति प्रयोग गर्नुहोस्).
- आदेश प्रयोग गरेर दोस्रो स्तम्भमा उत्पादनको नामबाट पाँच-अङ्कको लेख अलग गर्नुहोस् विभाजित स्तम्भ ट्याब परिवर्तन (रूपान्तरण - विभाजन स्तम्भ).
- अनावश्यक स्तम्भहरू मेटाउनुहोस् र राम्रो दृश्यताको लागि बाँकीको शीर्षकहरू पुन: नामाकरण गर्नुहोस्।
नतिजाको रूपमा, हामीले निम्न, धेरै रमाईलो तस्वीर प्राप्त गर्नुपर्छ:
यो हाम्रो फाइलको पानामा यो ennobled तालिका फिर्ता अपलोड गर्न बाँकी छ Handler.xlsx समूह बन्द गर्नुहोस् र डाउनलोड गर्नुहोस् (घर - बन्द र लोड) ट्याब गृहपृष्ठ:
अनुरोधमा फाइलको बाटो खोज्दै
अब हाम्रो क्वेरी "अन्डर द हुड" कस्तो देखिन्छ हेरौं, पावर क्वेरीमा संक्षिप्त नाम "M" मा निर्मित आन्तरिक भाषामा। यो गर्नको लागि, दायाँ फलकमा डबल क्लिक गरेर हाम्रो क्वेरीमा फर्कनुहोस् अनुरोध र जडानहरू र ट्याबमा समीक्षा छनौट उन्नत सम्पादक (हेर्नुहोस् - उन्नत सम्पादक):
खुल्ने विन्डोमा, दोस्रो पङ्क्तिले तुरुन्तै हाम्रो मूल अपलोड फाइलमा हार्ड-कोड गरिएको मार्ग प्रकट गर्दछ। यदि हामीले यो पाठ स्ट्रिङलाई प्यारामिटर, चर, वा एक्सेल पाना सेलको लिङ्कसँग बदल्न सक्छौं जहाँ यो पथ पूर्व-लेखिएको छ, त्यसपछि हामी यसलाई पछि सजिलै परिवर्तन गर्न सक्छौं।
फाइल मार्गको साथ स्मार्ट तालिका थप्नुहोस्
अहिलेको लागि Power Query बन्द गरौं र हाम्रो फाइलमा फर्कौं Handler.xlsx। एउटा नयाँ खाली पाना थप्नुहोस् र यसमा एउटा सानो "स्मार्ट" तालिका बनाउनुहोस्, जसको एकमात्र कक्षमा हाम्रो स्रोत डेटा फाइलको पूर्ण मार्ग लेखिनेछ:
नियमित दायराबाट स्मार्ट तालिका बनाउनको लागि, तपाइँ किबोर्ड सर्टकट प्रयोग गर्न सक्नुहुन्छ Ctrl+T वा बटन तालिकाको रूपमा ढाँचा गर्नुहोस् ट्याब गृहपृष्ठ (घर - तालिकाको रूपमा ढाँचा)। स्तम्भ शीर्षक (सेल A1) बिल्कुल केहि पनि हुन सक्छ। यो पनि ध्यान दिनुहोस् कि स्पष्टताको लागि मैले तालिकाको नाम दिएको छु परिमिति ट्याब कन्स्ट्रक्टर (डिजाईन).
एक्सप्लोररबाट मार्ग प्रतिलिपि गर्न वा यसलाई म्यानुअल रूपमा प्रवेश गर्न पक्कै पनि गाह्रो छैन, तर मानव कारकलाई कम गर्न र सम्भव भएमा स्वचालित रूपमा मार्ग निर्धारण गर्नु उत्तम हुन्छ। यो मानक एक्सेल कार्यपत्र प्रकार्य प्रयोग गरेर लागू गर्न सकिन्छ सेल गर्नुहोस् (सेल), जसले तर्कको रूपमा निर्दिष्ट गरिएको कक्षको बारेमा उपयोगी जानकारीको गुच्छा दिन सक्छ - हालको फाइलको मार्ग सहित:
यदि हामी मान्दछौं कि स्रोत डेटा फाइल सधैं हाम्रो प्रोसेसरको रूपमा एउटै फोल्डरमा हुन्छ, त्यसपछि हामीलाई आवश्यक पथ निम्न सूत्रद्वारा गठन गर्न सकिन्छ:
=LEFT(CELL("फाइलनाम");FIND("[";CELL("फाइलनाम"))-1)&"शीर्ष १०० products.xls"
वा अंग्रेजी संस्करणमा:
=बायाँ(सेल(«फाइलनाम»);FIND(«[«;CELL(«फाइलनाम»))-१)&»TOп-1 товаров.xls»
... कार्य कहाँ छ LEVSIMV (बायाँ) पूर्ण लिङ्कबाट खोल्ने वर्ग कोष्ठक (अर्थात् हालको फोल्डरको बाटो) सम्मको पाठको टुक्रा लिन्छ, र त्यसपछि हाम्रो स्रोत डेटा फाइलको नाम र विस्तार यसमा टाँसिएको हुन्छ।
क्वेरीमा पथ प्यारामिटराइज गर्नुहोस्
अन्तिम र सबैभन्दा महत्त्वपूर्ण टच रहन्छ - अनुरोधमा स्रोत फाइलमा मार्ग लेख्न शीर्ष 100 products.xls, हाम्रो सिर्जना गरिएको "स्मार्ट" तालिकाको सेल A2 लाई सन्दर्भ गर्दै परिमिति.
यो गर्नको लागि, पावर क्वेरी क्वेरीमा फर्कनुहोस् र यसलाई फेरि खोल्नुहोस् उन्नत सम्पादक ट्याब समीक्षा (हेर्नुहोस् - उन्नत सम्पादक)। उद्धरणहरूमा पाठ स्ट्रिङ-पथको सट्टा "E: बिक्री रिपोर्टहरू शीर्ष 100 products.xlsx" निम्न संरचना परिचय गरौं:
Excel.CurrentWorkbook(){[नाम="सेटिङ्हरू"][सामग्री]० {}[स्रोत डेटाको लागि मार्ग]
यसमा के समावेश छ हेरौं:
- Excel.CurrentWorkbook() हालको फाइलको सामग्री पहुँच गर्नको लागि M भाषाको कार्य हो
- {[नाम="सेटिङ्हरू"][सामग्री] - यो अघिल्लो प्रकार्यको परिमार्जन प्यारामिटर हो, यसले संकेत गर्दछ कि हामी "स्मार्ट" तालिकाको सामग्रीहरू प्राप्त गर्न चाहन्छौं। परिमिति
- [स्रोत डेटाको लागि मार्ग] तालिकामा रहेको स्तम्भको नाम हो परिमितिजसलाई हामी सन्दर्भ गर्छौं
- ० {} तालिकामा पङ्क्ति नम्बर हो परिमितिजसबाट हामी डाटा लिन चाहन्छौं। क्यापले गणना गर्दैन र नम्बरिङ शून्यबाट सुरु हुन्छ, एकबाट होइन।
त्यो सबै हो, वास्तवमा।
यसमा क्लिक गर्न बाँकी छ समाप्त र हाम्रो अनुरोध कसरी काम गर्छ जाँच गर्नुहोस्। अब, जब सम्पूर्ण फोल्डर दुबै फाइलहरू भित्र अर्को पीसीमा पठाइन्छ, अनुरोध परिचालन रहनेछ र स्वचालित रूपमा डाटाको मार्ग निर्धारण गर्दछ।
- Power Query के हो र Microsoft Excel मा काम गर्दा यो किन आवश्यक छ
- Power Query मा फ्लोटिंग टेक्स्ट स्निपेट कसरी आयात गर्ने
- पावर क्वेरीको साथ फ्ल्याट टेबलमा XNUMXD क्रसट्याब पुन: डिजाइन गर्दै