एक्सेल एमवीपी पावर क्वेरी में डेटा क्लींजिंग समस्या पर हमला करते हैं - एक्सेल टिप्स

विषय - सूची

ध्यान दें

यह पॉडकास्ट 2316 चुनौती के लिए भेजे गए समाधानों का विवरण देने वाले लेखों की एक श्रृंखला है।

YouTube पर Excel से फायर चैनल पर Excel MVP Oz Du Soleil ने ब्राज़ीलियाई बुल राइडर काइके पाचो का उल्लेख किया। ओज पहले व्यक्ति थे जिन्होंने ध्यान दिया कि मैं चार तिमाहियों को जोड़ने का सबसे धीमा तरीका था।

ओज का वीडियो है:
https://www.youtube.com/watch?v=OluZlF44PNI

उसका कोड है:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Removed Columns" = Table.RemoveColumns(Source,("Column2", "Column3", "Column4", "Column5", "Column6")), #"Transposed Table" = Table.Transpose(#"Removed Columns"), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Administrative", type number), ("Holiday", Int64.Type), ("PTO/LOA/Jury Duty", Int64.Type), ("Project A", type number), ("Project B", type number), ("Project C", type number))), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if (Category Description) = "Q1" then null else if (Category Description) = "Q2" then null else if (Category Description) = "Q3" then null else if (Category Description) = "Q4" then null else (Category Description)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Custom")), #"Renamed Columns" = Table.RenameColumns(#"Filled Down",(("Custom", "Names"))), #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each (Category Description) = "Q1" or (Category Description) = "Q2" or (Category Description) = "Q3" or (Category Description) = "Q4"), #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",("Names", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Names", "Category Description"), "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"(#"Category Description")), "Category Description", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Addition", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Renamed Columns1" = Table.RenameColumns(#"Inserted Sum",(("Addition", "TOTAL"))) in #"Renamed Columns1"

एक और समाधान, एक्सेल एमवीपी जॉन मैकडॉगल से यह एक।

जॉन ने पहली बार कहा था कि पावर क्वेरी को जोड़े गए दो अतिरिक्त चरणों को हटाकर, आप डुप्लिकेट Q1 Q2 Q3 Q4 शीर्षकों पर विषम प्रत्ययों को समाप्त करते हैं।
जॉन ने एक इंडेक्स कॉलम का उपयोग किया जो कि छँटाई के लिए अंत में इस्तेमाल किया जाएगा। लेकिन - जॉन ने श्रेणी विवरण के बाद अपने सूचकांक कॉलम को संक्षिप्त कर दिया। उन्होंने एक ऊर्ध्वाधर पाइप चरित्र का उपयोग किया इसलिए वह बाद में डेटा को तोड़ सकता है।
जॉन ने सशर्त कॉलम इंटरफ़ेस का उपयोग करने के बजाय एक कस्टम कॉलम के रूप में अपने सशर्त कॉलम को टाइप किया।

कस्टम कॉलम के रूप में सशर्त स्तंभ

यहां देखें जॉन का वीडियो:
https://www.youtube.com/watch?v=Dqmb6SEJDXI

एमवी के सह-लेखक एक्सेल एमवीपी केन पल्स तीन समाधानों में भेजी गई (डेटा) मंकी बुक के लिए है। उनका सशर्त स्तंभ संभवतः सबसे छोटा है।

लेकिन केन का पसंदीदा समाधान मूल प्रश्न की उपेक्षा करता है। पावर क्वेरी में तालिका बनाने के बजाय, वह पावर क्वेरी में सेट किया गया एक पिवटेबल डेटा बनाता है और फिर पिवट टेबल के साथ समाप्त होता है।

पावर क्वेरी में केन का अंतिम पूर्वावलोकन इस तरह दिखता है:

निर्णायक डेटा सेट

यहाँ केन का कोड है:

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))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type",("Dept. Total", "Q1", "Q2", "Q3", "Q4")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", ("Category Description"), "Attribute", "Value"), #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Other Columns", "Employee", each if Text.Contains((Attribute), "_") then null else (Attribute)), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee")), #"Split Column by Delimiter" = Table.SplitColumn(#"Filled Down", "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))), #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ((Attribute.2) null)), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",(("Attribute.1", "Quarter"), ("Value", "Amount"))), #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",(("Category Description", type text), ("Quarter", type text), ("Amount", type number), ("Employee", type text))) in #"Changed Type2"

इस क्वेरी को केवल एक कनेक्शन के रूप में बनाने के बाद, वह अंतिम रिपोर्ट बनाने के लिए एक पिवट टेबल का उपयोग करता है।

पिवट टेबल के साथ अंतिम रिपोर्ट

अन्य MVP से समाधान:

Wyn हॉपकिंस कोड यहाँ है: पावर क्वेरी: एकाधिक पहचानकर्ताओं के साथ व्यवहार करना।
माइक गिरविन का कोड यहां है: पावर क्वेरी: एक कॉलम से लेफ्ट 2 कैरेक्टर्स को निकालना।
रोजर गोविर का सूत्र समाधान यहाँ है: सूत्र समाधान।

पॉडकास्ट 2316 चुनौती के लिए मुख्य पृष्ठ पर लौटें।

इस श्रृंखला में अगला लेख पढ़ें: पावर क्वेरी: उपयोगकर्ता इंटरफ़ेस से परे: Table.Split और अधिक।