पावर क्वेरी के साथ क्लीन डेटा - एक्सेल टिप्स

विषय - सूची

वीडियो देखेंा

Microsoft से डेटा निकालने, ट्रांसफ़ॉर्म करने और लोड करने के लिए पावर क्वेरी एक नया टूल है। आज का लेख एक फ़ोल्डर में सभी फाइलों को संसाधित करने के बारे में है।

Power Query को Excel 2016 में बनाया गया है और यह Excel 2010 और Excel 2013 के कुछ संस्करणों में मुफ्त डाउनलोड के रूप में उपलब्ध है। उपकरण को विभिन्न स्रोतों से Excel में डेटा निकालने, बदलने और लोड करने के लिए डिज़ाइन किया गया है। सबसे अच्छा हिस्सा: पावर क्वेरी आपके चरणों को याद करती है और जब आप डेटा को ताज़ा करना चाहते हैं तो उन्हें वापस खेलेंगे। जैसे ही यह पुस्तक प्रेस में जाती है, एक्सेल 2016 में पावर क्वेरी सुविधाएँ नई क्वेरी के तहत गेट एंड ट्रांसफ़ॉर्म समूह में डेटा टैब पर होती हैं। यह अनुमान लगाना कठिन है कि क्या Microsoft Excel 2010 और Excel 2013 में पॉवर क्वेरी को गेट एंड ट्रांसफ़ॉर्म में बदल देगा।

नया प्रश्न

यह मुफ्त ऐड-इन इतना अद्भुत है, इसके बारे में पूरी किताब हो सकती है। लेकिन मेरे शीर्ष 40 सुझावों में से एक के रूप में, मैं कुछ बहुत ही सरल कवर करना चाहता हूं: एक्सेल में फ़ाइलों की एक सूची लाना, साथ ही फ़ाइल निर्माण की तारीख और शायद आकार। यह बजट कार्यपुस्तिकाओं की सूची या फ़ोटो की सूची बनाने के लिए उपयोगी है।

Excel 2016 में, आप डेटा, नई क्वेरी, फ़ाइल से, फ़ोल्डर से चुनें। पहले के Excel संस्करणों में, Power Query, From File, From Folder का उपयोग करें। फ़ोल्डर निर्दिष्ट करें:

फ़ोल्डर निर्दिष्ट करें

क्वेरी को संपादित करते समय, किसी भी कॉलम को राइट-क्लिक करें जो आप नहीं चाहते हैं और निकालें चुनें।

अवांछित कॉलम निकालें

फ़ाइल का आकार प्राप्त करने के लिए, इस आइकन पर क्लिक करें विशेषताएँ कॉलम:

फाइल का आकार

अतिरिक्त विशेषताओं की एक सूची दिखाई देती है। नाप चुनें।

गुण

ट्रांसफ़ॉर्म विकल्पों की एक बड़ी सूची उपलब्ध है।

रूपांतरण विकल्प

मुर्गी आप क्वेरी को संपादित कर रहे हैं, बंद करें और लोड करें पर क्लिक करें।

बंद करें और लोड करें

Excel में डेटा तालिका के रूप में लोड होता है।

एक तालिका के रूप में एक्सेल में डेटा भार

बाद में, तालिका को अपडेट करने के लिए, डेटा का चयन करें, सभी को ताज़ा करें। Excel सभी चरणों को याद करता है और फ़ोल्डर में फ़ाइलों की वर्तमान सूची के साथ तालिका को अपडेट करता है।

पूर्व में पावर क्वेरी के रूप में जानी जाने वाली सुविधा के संपूर्ण विवरण के लिए, केन पल्स और मिगुएल एस्कोबार द्वारा एम (डेटा) बंदर की जांच की जाती है।

M (DATA) MONKEY »के लिए है

पावर क्वेरी को नामित करने के लिए मिगुएल एस्कोबार, रॉब गार्सिया, माइक गिर्विन, रे हौसर और कॉलिन माइकल का धन्यवाद।

वीडियो देखेंा

Excel 2016 में पावर क्वेरी उपकरण डेटा टैब पर हैं
2010 और 2013 के लिए मुफ्त ऐड-इन
Power Query का उपयोग करके किसी फ़ोल्डर की सभी फ़ाइलों को Excel ग्रिड में सूचीबद्ध करें
नई क्वेरी, फ़ाइल से, फ़ोल्डर से चुनें
स्पष्ट नहीं: आकार पाने के लिए विशेषता फ़ील्ड का विस्तार करें
यदि आपका डेटा CSV फ़ाइलों में है, तो आप सभी फ़ाइलों को एक ही बार में एक ही ग्रिड में आयात कर सकते हैं
शीर्षक पंक्ति को बढ़ावा देना
शेष शीर्ष लेख पंक्तियों को हटाएँ
नल के साथ "" बदलें
रूपरेखा दृश्य के लिए नीचे भरें
भव्य कुल स्तंभ हटाएं
डेटा को अनपिव करें
महीने के नामों को तारीखों में बदलने का फॉर्मूला
चरणों की पूरी सूची - दुनिया का सबसे बड़ा पूर्ववत
अगले दिन - सभी चरणों को फिर से करने के लिए क्वेरी को ताज़ा करें

वीडियो का ट्रांसक्रिप्ट

Power Query, Excel 2016 के Windows संस्करणों में अंतर्निहित है। गेट एंड ट्रांसफ़ॉर्म समूह में डेटा टैब को देखें। अगर आपके पास 2010 या है
2013 जब तक आप विंडोज चला रहे हैं
और मैक यहाँ सब कुछ नहीं है जो गेट एंड ट्रांसफ़ॉर्म में है
आप Microsoft से मुफ्त में डाउनलोड कर सकते हैं। बस खोजते हैं
पावर क्वेरी डाउनलोड करें।
आज, मैं एक फ़ाइल सूची प्राप्त करने के लिए पावर क्वेरी का उपयोग करने में रुचि रखता हूं। मैं
एक फ़ोल्डर में सभी फाइलों को सूचीबद्ध करना चाहते हैं।
शायद मुझे यह देखने की जरूरत है कि कौन सी फाइलें हैं
बड़ी फ़ाइलों या मुझे सॉर्ट करने की आवश्यकता है या मुझे ज़रूरत है
आप का एक संयोजन पाने के लिए पता है
हमारे द्वारा भेजी गई बजट फ़ाइलों को जानें
और फिर एक अलग फ़ोल्डर जो
हम वापस आ गए।
शुरू करने के लिए, डेटा, गेट और ट्रैन्फॉर्म, फाइल से, फोल्डर पर जाएं।
फ़ोल्डर पथ में पेस्ट करें या ब्राउज़ बटन का उपयोग करें।
ठीक पर क्लिक करें और वे मुझे यह दिखाते हैं
पूर्व दर्शन। संपादित करें चुनें।
कुछ चीज़ें यहाँ आप देख रहे हैं हमारे पास
फ़ाइल का नाम एक्सटेंशन की तारीख है
पहुँच, दिनांक संशोधित, दिनांक बनाई गई।
यह वास्तव में स्पष्ट नहीं है कि गुण शीर्षक के बगल में यह प्रतीक विस्तार का मतलब है। उस प्रतीक पर क्लिक करें और उसमें अधिक सामान है
यहाँ और यदि आप इस प्रतीक पर क्लिक करते हैं तो मैं
में जा सकते हैं और फ़ाइल आकार जैसी चीजें प्राप्त कर सकते हैं
या अगर यह केवल पढ़ने के लिए और चीजों की तरह है
इसलिए इस मामले में मुझे सिर्फ फाइल चाहिए
आकार। फ़ाइल का आकार चुनें। ओके पर क्लिक करें। वे आपको Attributes.Size के नाम से एक नया क्षेत्र देते हैं।
मैं देख सकता हूं कि कितने बाइट हैं
प्रत्येक फ़ाइल।
शायद मुझे यहाँ सब कुछ की आवश्यकता न हो
मुझे बनाई गई तिथि की आवश्यकता नहीं है इसलिए मैं कर सकता हूं
राइट क्लिक करें और कहें कि मैं चाहता हूं
उस कॉलम को हटा दें। यह
बाइनरी मुझे जरूरत नहीं है कि निकाल देंगे
वह कॉलम। रिबन से, बंद करें और लोड करें पर क्लिक करें।
कुछ ही सेकंड में, आपके पास एक सॉर्टेबल दृश्य होगा
उस फ़ोल्डर में सब कुछ अगर फ़ोल्डर
परिवर्तन मैं यहाँ आ सकता हूँ और मैं कर सकता हूँ
क्वेरी को रिफ्रेश करें और वह वापस चली जाएगी
सही में यह डेटा बाहर और खींचो
मेरे लिए यह एक समस्या है जिसका हम उपयोग करते थे
हर समय हम 200 बाहर भेजते हैं
बजट फाइलें
और तुम किसी को वापस नहीं पाओगे
वापस आपको इसकी तुलना करने में सक्षम होने की आवश्यकता है
अब मैं अनिवार्य रूप से एक vlookup कर सकता हूं
फ़ोल्डरों के बीच।
यह आश्चर्यजनक है कि कैसे
यह शांत है, लेकिन देखो हम आगे बढ़ें
मेरे पास पुस्तक में क्या है और आपको कैसे दिखाता हूँ
यह सिर्फ हिमशैल के टिप है।
मैं एक और क्वेरी बनाने जा रहा हूँ। डेटा, नई क्वेरी, फ़ाइल से, फ़ोल्डर से।
मैं उस फ़ोल्डर पथ को यहाँ कॉपी करूँगा।
संपादित करें पर क्लिक करें।
अक्टूबर 2016 तक, यह ट्रिक केवल CSV के साथ काम करती है
फाइलें, लेकिन 2017 में इसे सिंगल-शीट एक्सेल फाइलों के साथ काम करने के लिए अपडेट किया गया था। मैं
एक फ़ोल्डर फ़ाइलों का एक पूरा गुच्छा है और
मैं सभी के साथ एक एक्सेल ग्रिड बनाना चाहता हूं
इन सभी फ़ाइलों से डेटा का।
यह सहज नहीं है। बाइनरी कॉलम के लिए शीर्षक के बगल में देखें। क्षैतिज रेखा पर नीचे की ओर इंगित करने वाले दो तीरों वाला एक आइकन है।
उस पर क्लिक करें।
बाम! यह बस से हर एक रिकॉर्ड में खींच लिया
उस फ़ोल्डर में हर एक फ़ाइल!
नहीं है
यह आश्चर्यजनक है कि मेरा मतलब है कि एक VBA मैक्रो था
पहले और VBA सीखने में कई महीने लगते हैं
मैक्रोज़ आप दस में पावर क्वेरी सीख सकते हैं
मिनट।
हमें इस कॉलम का चयन करना है और
go to replace values say that we're
going to
replace nothing with the word null click
okay
That'll give us Nulls in place of empty cells.
Those nulls allow us to use this amazing
featured called Fill Down. Watch that
column when I choose Fill Down. BAM it
just pulled in all of that outline view
and brought the value down.
I don't need the Grand Total column.
Right-click and remove.
Now at this point you say oh yeah hey we could
pull this in and it'd be awesome. But if
we wanted to create a pivot table from
this data having a repeating group going
across Jan Feb Mar is not a good format
for a pivot tables.
Right now we have 47
rows I need to have 47 times twelve rows
and to do this in a regular Excel file
it is horrendous using a Multiple
Consolidation Range that I learned from
Mike Alexander at Data Pig Technologies.
But it is easy in Power Query. Check this out I'm going to choose the
label columns along the left. These are the things that I don't
want to change and then on the Transform tab, choose Unpivot Other Columns.
We go from 47 rows to 564 rows
that's an amazing step.
Here you can see
that these values are text. It is easy enough to
change it to either currency or a whole
number. Right click the heading and choose Rename and call it
revenue
How about these months? They're
all text such as Jan, Feb, Mar. Here's an awesome way to fix
that we go to add column add a custom
column doesn't matter what the name you use.
The calculation, in quotes, is " 1, 2016". Click OK.
Now we have this new custom column I'm going to take the
attribute column containing Month names and the new custom column. Select both columns
and say I want to merge those columns
with a space in between and call it date.
Click OK. That looks enough
like a real date that when I go to
transform and change it to a date it
converts it to a true Excel date.
At this point these two temporary
columns I can right click and remove.
Now you could be saying to yourself:
Wait, Bill! we could have done all of this in Excel and that's absolutely true we could have done all of it in Excel it would have been harder to get all the CSV files into one file it would have taken longer to fill in the blanks it definitely would have taken longer to do the unpivot operation but here's the thing look over on the right-hand side we haven't talked about Applied Steps at all. The Applied Steps panel is like the world's greatest undo if you need an audit trail if the auditors come and say well how did you get from all these CSV files to this file that we're building our financial statements on you can go back and show what it looked like at each step along the way. If you screwed something up back here you could change or edit that step. Next, on the Power Query Home tab, choose Close and Load. So here's our data set this is based on all of the files in this folder. Let's build a little pivot table from here insert pivot table existing worksheet right here and I'll put revenue in the values area products down the left hand side you see that we have six million in revenue. Well that's today's data now tomorrow tomorrow let's say that we get a couple of new customers a couple of new files come along and our IT department takes those and dumps them into our folder all I have to do is reopen this file select the query come over here and refresh and then come here analyze refresh the pivot table and we have the new data! Power Query is faster on day one maybe by a factor of 20-30%. On day two is faster by 99% it's an absolutely amazing product Power Query. It's in Excel 2016 but if you're in 2010 or 2013 for Windows you're more than welcome to go out and download it for free. The book that will teach you about Power Query is M is for (Data) Monkey" by Ken Puls and Miguel Escobar.
यह किताब पढ़ाएगी
आप पावर क्वेरी के बारे में सब कुछ
इंटरफ़ेस यह एक अद्भुत पुस्तक सबसे अच्छा है
पावर क्वेरी पर पुस्तक जो मैंने सीखा है
मैंने इस पुस्तक से सीखा। मैं फ्लाइट से गया
ऑरलैंडो टू डलास - मैंने पूरी किताब पढ़ी
और शक्ति क्वेरी का मेरा ज्ञान बस
आप दो घंटे तक बढ़ सकते हैं
गति और चीजों को प्रतिस्थापित करें जो आप करेंगे
VBA के साथ किया करते थे।