जब मैं एक लाइव पावर एक्सेल सेमिनार करता हूं, तो मैं प्रस्ताव देता हूं कि अगर कमरे में किसी को कभी भी एक्सेल की समस्या है, तो वे इसे मेरी मदद के लिए भेज सकते हैं। इस तरह से मुझे यह डेटा क्लींजिंग समस्या प्राप्त हुई। किसी के पास एक सारांश कार्यपत्रक था जो इस तरह दिखता है:
वे इस तरह देखने के लिए डेटा को पुन: स्वरूपित करना चाहते थे:
इस डेटा के बारे में एक दिलचस्प सुराग: G4 में 18 H4: K4 का उप-योग प्रतीत होता है। यह कॉलम G, L, और इसी तरह के कॉलम को हटाने के लिए लुभाता है, लेकिन पहले आपको G3, L3 और इसी तरह के कर्मचारी का नाम निकालना होगा।
यह रविवार 9 फरवरी को 4AM था जब मैंने वीडियो रिकॉर्डर चालू किया और समस्या को हल करने के लिए पावर क्वेरी में कुछ क्लूनी चरण दर्ज किए। यह देखते हुए कि यह रविवार था, एक दिन जो मैं सामान्य रूप से वीडियो नहीं करता, मैंने लोगों से अपने विचारों को भेजने के लिए कहा कि समस्या को कैसे हल किया जाए। 29 समाधान भेजे गए हैं।
प्रत्येक समाधान मेरी प्रक्रिया पर कुछ अच्छा नया सुधार प्रदान करता है। मेरी योजना लेखों की एक श्रृंखला शुरू करने की है जो मेरी पद्धति में विभिन्न सुधारों को दिखाती है।
वीडियो देखेंा
इससे पहले कि मैं इस प्रक्रिया को शुरू करूं, मैं आपको अपना समाधान देखने के लिए आमंत्रित करता हूं:
और एम-कोड जो पावर क्वेरी मेरे लिए उत्पन्न हुआ:
let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Promoted Headers" = Table.PromoteHeaders(Source, (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Dept. Total", type number), ("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", Int64.Type), ("Employee 1", type number), ("Q1_1", type number), ("Q2_2", type number), ("Q3_3", Int64.Type), ("Q4_4", Int64.Type), ("Employee 2", Int64.Type), ("Q1_5", Int64.Type), ("Q2_6", Int64.Type), ("Q3_7", Int64.Type), ("Q4_8", Int64.Type), ("Employee 3", Int64.Type), ("Q1_9", Int64.Type), ("Q2_10", Int64.Type), ("Q3_11", Int64.Type), ("Q4_12", Int64.Type), ("Employee 4", type number), ("Q1_13", type number), ("Q2_14", type number), ("Q3_15", type number), ("Q4_16", Int64.Type))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", ("Category Description"), "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter(("_"), QuoteStyle.Csv, false), ("Attribute.1", "Attribute.2")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Attribute.1", type text), ("Attribute.2", Int64.Type))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",(("Attribute.1", "TextValue"))), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Length", each Text.Length((TextValue))), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Name", each if (Length)> 2 then (TextValue) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Name")), #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",("Category Description", "Name", "TextValue", "Value", "Length")), #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ((TextValue) = "Q1" or (TextValue) = "Q2" or (TextValue) = "Q3" or (TextValue) = "Q4")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each (Name) "Dept. Total"), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",("Length")), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"(TextValue)), "TextValue", "Value", List.Sum), #"Sorted Rows" = Table.Sort(#"Pivoted Column",(("Name", Order.Ascending))), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Total", each (Q1)+(Q2)+(Q3)+(Q4)) in #"Added Custom1"इससे पहले कि हम समाधान के लिए शुरू हो, हम आम टिप्पणियों का एक बहुत कुछ पता है:
- आप में से कुछ ने कहा कि आप यह पता लगाने के लिए पीछे जाएंगे कि डेटा इस प्रारूप में क्यों दिख रहा है। मैं इन टिप्पणियों की सराहना करता हूं। हर किसी ने कहा कि मैं इससे बेहतर इंसान हूं। मैंने वर्षों से सीखा है कि जब आप पूछते हैं "क्यों?" उत्तर में आमतौर पर इस पूर्व कर्मचारी को शामिल किया जाता है जिसने 17 साल पहले इस रास्ते को शुरू किया था और हर कोई इसे इस तरह से उपयोग करता रहता है क्योंकि हम सभी अब इसका उपयोग कर रहे हैं।
- इसके अलावा - आप में से कई ने कहा कि अंतिम समाधान एक लंबी ऊर्ध्वाधर तालिका होनी चाहिए और फिर अंतिम परिणामों का उत्पादन करने के लिए एक धुरी तालिका का उपयोग करना चाहिए। जोनाथन कूपर ने इसे सबसे अच्छा बताया: "मैं कुछ अन्य YouTube टिप्पणियों से भी सहमत हूं, जो एक उचित डेटा सेट में" टोटल्स "नहीं होंगे और अंत में पिवोट होने की आवश्यकता नहीं होगी। लेकिन अगर उपयोगकर्ता वास्तव में एक सादा चाहता है। पुरानी तालिका फिर आप उन्हें वही दें जो वे चाहते हैं। " मैं वास्तव में इसके दोनों किनारों को देख सकता हूं। मुझे एक पिवट टेबल पसंद है और पॉवर क्वेरी की तुलना में केवल एक ही चीज़ अधिक मज़ेदार है, शीर्ष पर एक अच्छी पिवट टेबल के साथ पावर क्वेरी। लेकिन अगर हम पावर क्वेरी में पूरी बात कर सकते हैं, तो एक चीज को तोड़ने के लिए कम है।
यहाँ विभिन्न तकनीकों के लिए हाइपरलिंक हैं
-
पावर क्वेरी तकनीक
- अभिलेखों के समूह
- बायां दो वर्ण निकालना
- कुल स्तंभ
- यदि क्लॉस होता है
- मल्टीपल आइडेंटिकल हेडर्स इन पावर क्वेरी
- क्या हटाना है
- क्यू द्वारा विभाजित
- छँटाई लाइन आइटम
- एक्सेल एमवीपी से पावर क्वेरी समाधान
-
पावर क्वेरी इंटरफ़ेस से आगे बढ़ रहा है
- तालिका
- बिल स्काईज की दुनिया
-
सूत्र समाधान
- एक गतिशील सरणी फॉर्मूला
- पुराना स्कूल हेल्पर कॉलम
- सूत्र समाधान
-
उपरोक्त और अंतिम वीडियो से सभी विचारों की समग्रता
- सभी से सर्वश्रेष्ठ विचारों का सम्मिश्रण
