पावर क्वेरी में CONCATENATEX। नया TEXTJOIN फंक्शन कमाल का है। क्या आप पावर क्वेरी के साथ भी ऐसा ही कर सकते हैं? हाँ। अब आप कर सकते हैं।
वीडियो देखेंा
- एक दर्शक उस सिस्टम से डेटा डाउनलोड करता है जहां प्रत्येक आइटम को Alt + Enter द्वारा अलग किया जाता है
- बिल: आप ऐसा क्यों कर रहे हैं? दर्शक: यह है कि मैं कैसे डेटा विरासत में मिला है। मैं इसे इसी तरह रखना चाहता हूं।
- बिल: आप 40% मूल्यों के साथ क्या करना चाहते हैं तालिका में नहीं? दर्शक: कोई जवाब नहीं
- बिल: यदि आपके पास नवीनतम पावर क्वेरी उपकरण हैं, तो इसे हल करने का एक जटिल तरीका है।
- इसके बजाय, इसे हल करने के लिए एक VBA मैक्रो - मैक्रो को Excel 2007 में सभी तरह से काम करना चाहिए
- VLOOKUP करने के बजाय, VBA से फाइंड एंड रिप्ले की एक श्रृंखला करें
वीडियो ट्रांसक्रिप्ट
एक्सेल, पॉडकास्ट एपिसोड 2151 से सीखें।
मैं वास्तव में यह नहीं जानता कि इसे क्या कहा जाए। अगर मैं DAX का उपयोग करने वाले लोगों को आकर्षित करने की कोशिश कर रहा हूं, तो मैं कहूंगा कि ConcatenateX को पावर क्वेरी में, या केवल वे लोग जो नियमित Excel लेकिन Office 365 का उपयोग करते हैं, मैं कहूंगा कि TEXTJOIN in Power Query, या, पूरी तरह से ईमानदार होने के लिए, एक्सेल में एक सुपर-पागल समाधान को सक्षम करने के लिए पावर क्वेरी में चरणों का एक सुपर जटिल सेट।
अरे। नेटकास्ट में आपका स्वागत है। मैं बिल जेलन हूं। खैर, कल एपिसोड 2150 में, मैंने समस्या का वर्णन किया। इस फाइल में किसी को भेजा गया है जहां उनका सिस्टम उन वस्तुओं को डाउनलोड कर रहा है जो उनके बीच लाइनफीड के साथ एक ऑर्डर हैं। दूसरे शब्दों में, ALT + ENTER, और देखें, WRAP TEXT चालू है, और वे उन प्रत्येक आइटम के लिए इस LOOKUPTABLE में एक VLOOKUP करना चाहते हैं। मुझे पसंद है, क्या? आप यह क्यों कर रहे हैं? लेकिन मैंने उस कल को कवर किया। आइए बस कोशिश करें और यह पता करें कि यह कैसे करना है।
मैंने वास्तव में कहा, ठीक है, पावर क्वेरी ऐसा करने का सबसे अच्छा तरीका होगा, लेकिन मैं आखिरी भाग कैसे करना है, इस पर स्टम्प्ड हो गया। मैंने कहा, क्या यह ठीक है यदि प्रत्येक वस्तु अपनी पंक्ति में समाप्त हो जाए? नहीं, उन्हें इस मूल क्रम में वापस आना होगा। मैं पसंद कर रहा हूँ, यह भयानक है, लेकिन, पिछले हफ्ते मेरे ट्विटर फीड पर, टिम रोडमैन, 27 सितंबर: "अंत में इस पुस्तक को पढ़ते हुए," - मैं अनुमान लगा रहा हूं कि यह पॉवरपिव कीमिया है - "और पहले से ही उनकी कॉनसैटनेट एक्स की इच्छा है। ” जब मैं ऐसा कर रहा था, तो मैं एक स्मार्ट बन रहा था, पेराफास रोमैनक्स के लिए पूछ रहा था, लेकिन मैं वास्तव में कॉन्टेनेटएक्स चाहता था, और इसलिए टिम ने मुझे एक सिर दिया कि मैं अब पावर बीआई में ऐसा कर सकता हूं।
इसलिए, मैं अपने दोस्तों के लिए बाहर गया, रॉब कोली ने पिवोट प्रो और मिगुएल एस्कोबार, और, आप जानते हैं, वे दोनों महान पुस्तकों के लेखक हैं। मेरे पास ये दोनों पुस्तकें हैं, लेकिन यह विशेषता बहुत नई है, न ही किसी पुस्तक में। मैंने कहा, अरे, क्या तुम लोग जानते हो कि यह कैसे करना है? और मिगुएल पुरस्कार जीतता है क्योंकि मिगुएल आज सुबह या देर रात तक उठे हुए थे - मुझे यकीन नहीं है कि कौन सा - और कोड में भेजा गया है।
ठीक है, इसलिए, यहां पावर क्वेरी में योजना और यह एक बहुत जटिल है। मैं पावर क्वेरी में कोई योजना नहीं लिखता। मैं अभी पूरी चीजें करता हूं। मैं मूल डेटा के साथ शुरू करने जा रहा हूं, एक INDEX कॉलम जोड़ूंगा ताकि हम एक ऑर्डर से आइटम को एक साथ रख सकें, LINEFEED का उपयोग करके ROWS को COLLN करें। यह पॉडकास्ट पर दूसरी या तीसरी बार मैंने इस नई सुविधा का उपयोग किया है। कितना मजेदार था वो। मेरे पास एक दूसरा INDEX कॉलम था ताकि हम आइटम को मूल अनुक्रम में सॉर्ट कर सकें, और फिर एक कनेक्शन के रूप में बचाएं।
फिर, हम LOOKUP टेबल पर आने वाले हैं, इसे एक टेबल बनाएं, टेबल से क्वेरी करें, SAVE AS CONNECTION - जो कि इस समय सबसे आसान हिस्सा है - और फिर इस क्वेरी और इस क्वेरी को आइटम के आधार पर मर्ज करें संख्या, बाईं तालिका से सभी आइटम, यह बाईं तालिका है, दाईं ओर से मेल खाती है, आइटम नंबर के साथ नल को प्रतिस्थापित करें। हम अभी भी हवा में हैं कि हम क्या करना चाहते हैं जब किसी कारण से कुछ नहीं मिला। मैंने यह प्रश्न पूछा है, लेकिन फ़ाइल में भेजा गया व्यक्ति जवाब नहीं दे रहा है, इसलिए मैं इसे आइटम नंबर से बदलने जा रहा हूं। उम्मीद है कि सही बात यह है कि LOOKUPTABLE में अधिक आइटम जोड़े जाएं ताकि कोई भी वस्तु न मिले, लेकिन यहां हम हैं, और फिर हम INDEX1 और INDEX2 द्वारा सॉर्ट करने जा रहे हैं, इस तरह,चीजें सही क्रम में वापस आ गई हैं और फिर यह वह हिस्सा था जिसे मैं समझ नहीं पा रहा था कि कैसे करना है।
हम INDEX1 द्वारा समूह में जा रहे हैं जो TEXTJOIN या ConcatenateX के बराबर के साथ कर रहा है चरित्र 10 को विभाजक के रूप में, एग्रीगेटर के रूप में, और, निश्चित रूप से, यह वह हिस्सा है जो कठिन हिस्सा है लेकिन यह हिस्सा वास्तव में यहाँ नया है चरणों का यह सेट। इसलिए, यदि आप समझते हैं कि TEXTJOIN क्या करता है या यह अवधारणा कर सकता है कि ConcatenateX ने क्या किया होगा, तो हम अनिवार्य रूप से इस तरह के कदम का उपयोग कर रहे हैं। तो, ठीक है। इसलिए हमें कोशिश करनी चाहिए।
इसलिए, हम यहां शुरू करने जा रहे हैं। यहां हमारा मूल डेटा है, एक शीर्षक है। इसलिए, मैं TABLE, CONTROL + T, MY TABLE HAS HEADERS के रूप में FORMAT जा रहा हूं, हां, और फिर हम पावर क्वेरी का उपयोग करने जा रहे हैं। अब, मैं एक्सेल २०१६ ऑफिस ३६० में हूं, इसलिए यह डाटा टैब के बायें हिस्से पर है। यदि आप सीधे Excel 2016 में हैं, तो Office 365 नहीं, यह मध्य में है - GET & TRANSFORM। यदि आप Excel 2010 या 2013 में हैं, तो यह पावर टैबरी नामक अपना स्वयं का टैब आउट करने वाला है, और यदि आपके पास वह टैब नहीं है, तो आपको उस टैब को डाउनलोड करना होगा। यदि आप Mac या Android या Excel के किसी अन्य नकली संस्करण पर हैं, तो क्षमा करें, आपके लिए कोई पावर क्वेरी नहीं है। एक्सेल का विंडोज संस्करण प्राप्त करें और इसे आज़माएं।
ठीक है, इसलिए, हम एक टेबल से एक बिजली क्वेरी करने के लिए जा रहे हैं, ठीक है, और पहली बात जो मैं करने जा रहा हूँ, मैं एक INDD COLUMN जोड़ें और मैं FROM 1. शुरू करने जा रहा हूँ। , इसलिए, यह अनिवार्य रूप से आदेश 1, ऑर्डर 2, ऑर्डर 3, ऑर्डर 4 है। फिर हम इस कॉलम को चुनने जा रहे हैं, और ट्रांसफ़ॉर्म टैब पर, हम SPLIT COLUMN, DELIMITER द्वारा जा रहे हैं, और वे सक्षम थे पता लगाएँ कि यह एक लाइनफ़ीड है परिसीमनकर्ता है। मुझे लगता है कि पावर क्वेरी इस बात का पता लगा रही है। अब, Excel, स्तंभों पर पाठ, हाँ, स्तंभों पर पाठ यह पता क्यों नहीं लगाता है कि सीमांकक क्या है? और प्रत्येक घटना हम SPLIT INT ROWS, और USING SPECIAL CHARACTER में जा रहे हैं। ठीक है, इसलिए यह सब अच्छा है।
अब देखिये यहाँ क्या होता है। हमारी 999 पंक्तियाँ हैं लेकिन अब हमारे पास इससे कहीं अधिक हैं। तो, उस क्रम संख्या में प्रत्येक आइटम अब अपनी पंक्ति है। अब, जिस व्यक्ति ने यह प्रश्न पूछा है वह नहीं चाहता कि यह उसकी अपनी पंक्ति हो लेकिन हम इसे अपनी पंक्ति बनाने जा रहे हैं ताकि हम इसमें शामिल हो सकें। मैं यहां एक नया INDEX कॉलम जोड़ने जा रहा हूं। ADD COLUMN, INDEX COLUMN, FROM 1, और इसलिए हमारे पास … ये अनिवार्य रूप से आदेश संख्याएं हैं और फिर ये क्रम के भीतर अनुक्रम हैं क्योंकि मैंने निर्धारित किया है कि, बाद में, ये कुछ अन्य क्रम में होने जा रहे हैं। मुझे नहीं पता कि वे किस क्रम पर स्विच करते हैं, लेकिन यहां हम हैं।
ठीक है, तो, घर, CLOSE और लोड बटन नहीं, लेकिन CLOSE और लोड ड्रॉप-डाउन, और CLOSE और लोड करें। मुझे नहीं पता कि पहली बार इस डायलॉग बॉक्स को प्रदर्शित करने में उन्हें 10 सेकंड क्यों लगे। हम केवल निर्माण के लिए जा रहे हैं। ओके पर क्लिक करें। सुंदर। तो वह है TABLE1, TABLE1।
अब, हम अपने LOOKUPTABLE पर जाने वाले हैं। LOOKUPTABLE को प्रोसेस करना आसान होने जा रहा है। हम इसे एक तालिका के रूप में प्रारूपित करने जा रहे हैं। नियंत्रण + टी। ओके पर क्लिक करें। यदि आप पुराने संस्करण से हैं, तो डेटा, या पावर क्वेरी। इसे TABLE2 कहा जा रहा है। इसे LOOKUPTABLE कहते हैं। उत्तम। बंद और लोड, बंद और लोड करने के लिए, केवल निर्माण।
ठीक है। अब, हमारे यहाँ दो बिट्स हैं और मैं उन दोनों को मिलाना चाहता हूँ। इसलिए, हम बस एक नए स्थान पर जाने वाले हैं और फिर डेटा, गेट डेटा, कॉम्बिनेशन क्वेरियाँ, हम एक MERGE करने जा रहे हैं, और बाईं ओर टेबल TABLE1 होने जा रहा है - यही हमारा मूल डेटा है - - और हम इस ITEM नंबर का उपयोग करने जा रहे हैं और हम LOOKUPTABLE तक और उस ITEM नंबर से शादी करने जा रहे हैं। यह वास्तव में गैर-सहज ज्ञान युक्त है कि आपको दोनों मामलों में ITEMS पर क्लिक करना होगा और यह परिभाषित करना होगा कि कुंजी क्या है, और एक OUTER शामिल हो सकता है, सभी से पहले, दूसरे से मिलान, और, देखें, इनमें से 40% ऐसे हैं जो गायब हैं। खोज तालिका। यह सभी नकली डेटा है लेकिन मूल डेटा के साथ ही LOOKUPTABLE से 40% गायब था। वास्तव में निराशा होती है। ठीक है। तो, यहाँ हमारा ITEM नंबर, हमारे 2 INDEX फ़ील्ड, और फिर यहाँ हमारा LOOKUPTABLE है। मैं'मीटर है कि करने के लिए जा रहा हूँ और वर्णन के लिए पूछना। ठीक है, आप देख रहे हैं कि हमारे यहाँ नल का एक गुच्छा है।
Alright, so, we're going to do a conditional column. Conditional column’s going to say look at this column. If it's = to null, then bring this value over, otherwise, use the value that's in that column. So here, under ADD COLUMN, we’ll do CONDITIONAL COLUMN -- nice little UI that'll walk us through this -- if the LOOKUPTABLEDESCRIPTION EQUALS NULL, then we want to use a COLUMN here of ITEMS, otherwise, we want to use the COLUMN called LOOKUPDESCRIPTION, alright. Click OK, and there we are. There's our CUSTOM column with either the new value from the LOOKUPTABLE or the original value if it's not found. At this point, we can right click and say that we want to REMOVE this column. It was a temporary column, it was a helper column. Now that we have what we need, we don't need that column anymore, and actually, at this point, I don't need this column anymore either. So, I can right click and REMOVE that column. Alright. Now, we have our data here. I want to sort it by the original INDEX. So, SORT ASCENDING. That gets our data into the right sequence, and now that it's sorted, I can actually right click and REMOVE that column.
Alright. Now we’re at the point where, for every item, each order number -- so, this is order number 1, let's say -- I want to have these 4 items separated by a LineFeed character. Now, what I was hoping to be able to do was to come here to TRASNFORM. Instead, if we wanted to GROUP BY and that there'd be some magic here in the GROUP BY, I would say I'm going to concatenate or textjoin all those things, but it doesn't work, alright?
So, here's the set of steps that are new to me that allow this to happen. First thing we want to do is we're going to create a brand new column. That column is just going to be called a TABLECOLUMN and we're going to take ALL ROWS and click OK. Okay. So, when we look inside at this table, we see that we have 2 columns -- one called INDEX and one called CUSTOM -- and we have to remember that name there, alright, and this table unfortunately does not work with STRUCTURED COLUMN. See, EXTRACT VALUES is grayed out. So, this doesn't work with a table. It has to work with a list. I have to convert this table to a list, and this is the part I couldn't figure out and the part that Miguel filled in for me.
So, I'm going to create a CUSTOM COLUMN here and I'm going to call it a LISTCOLUMN and we're going to use a function called TABLE.COLUMN and the table is the thing called TABLECOLUMN, and then which column in there is the thing called CUSTOM. Click OK. Alright, and now these are, instead of a table, it is a list. We're home-free now. TRANSFORM, STRUCTURED COLUMN. I'm going to EXTRACT VALUES. I'm going to create a CUSTOM delimiter USING SPECIAL CHARACTERS, INSERT SPECIAL CHARACTER, LINE FEED, and click OK, and it gives me what I'm looking for. So here’s my original order number. The TABLE, we don’t need anymore, right click and REMOVE that, and we now have our original data using the LOOKUPTABLE where we need it, alright? So, I can right-click and REMOVE this, alright, and then finally, HOME, just straight CLOSE & LOAD, which brings it back into a table in Excel. (=Table.Column((TableColumn),“Custom”))
Alright, but it doesn't look like it worked, does it? That's because, by default, this table does not have WRAP TEXT turned on. So, HOME, WRAP TEXT, and we now have our new data doing the equivalent of a VLOOKUP for each item in the list, and when an item is not found, the original item number is still there, so someone can go piece that back together.
Now, the beautiful thing with Power Query is that while it took us some time to get this set up the first time, the next time we download this list, we just copy it here, and we can even edit something. So, let’s change one. So, MANGO, 4954, we’ll take that 7036 and change it to 4954. Alright, so, now the underlying data has changed, all we have to do is come here to this and click the REFRESH which will refresh all of these items, and we get here to SHEET11 and that second item has changed to a MANGO, alright? It’ll take you some time to set this up once but, once you get it set up, it's just a simple matter of refreshing the data and Power Query will go through all those steps.
अब, हे, यह वह बिंदु है जहां मैं आमतौर पर आपसे मेरी पुस्तक खरीदने के लिए कहता हूं लेकिन, आज, बजाय इसके कि आप मिगुएल की पुस्तक खरीदने के लिए कहें। मिगुएल एस्कोबार और केन पल्स ने M Is For (DATA) MONKEY पर यह उत्कृष्ट पुस्तक लिखी - पॉवर क्वेरी पर सबसे अच्छी पुस्तक है। जाओ कि बाहर की जाँच करें।
ठीक है, लपेटो: आज एक बहुत लंबी कड़ी है; हमारे पास एक दर्शक है, एक ऐसी प्रणाली से डेटा डाउनलोड करता है, जहां प्रत्येक आइटम को ALT + ENTER द्वारा अलग किया जाता है और हम प्रत्येक व्यक्तिगत आइटम के लिए एक VLOOKUP करने की कोशिश कर रहे हैं; के रूप में निकालने के संरचित स्तंभ उपकरण सहित पावर क्वेरी का उपयोग करके आज एक समाधान बनाया गया; लेकिन यह केवल एक सूची पर काम करता है, तालिका नहीं, इसलिए मुझे तालिका को सूची में बदलने के लिए TABLE.COLUMN फ़ंक्शन का उपयोग करना पड़ा।
अच्छा हे। मैं आपको रोकने के लिए धन्यवाद देना चाहता हूं। हम आपको अगली बार एक और नेटकास्ट से देखेंगे।
फ़ाइल डाउनलोड करें
यहाँ नमूना फ़ाइल डाउनलोड करें: Podcast2151.xlsm
