Excel MVPs Επίθεση του προβλήματος εκκαθάρισης δεδομένων στο Power Query - Excel Συμβουλές

Πίνακας περιεχομένων

Σημείωση

Αυτό είναι ένα από μια σειρά άρθρων που περιγράφουν λεπτομερώς τις λύσεις που αποστέλλονται για την πρόκληση Podcast 2316.

Το Excel MVP Oz Du Soleil από το κανάλι Excel on Fire στο YouTube ανέφερε τον βραζιλιάνο Bull Rider Kaique Pachecho. Ο Οζ ήταν το πρώτο άτομο που παρατήρησε ότι πήγα με αργό τρόπο για να προσθέσω τα τέσσερα τέταρτα.

Το βίντεο του Oz είναι:
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"

Μια άλλη λύση, αυτή από το Excel MVP John MacDougall.

  • Ο John ήταν ο πρώτος που είπε ότι διαγράφοντας τα δύο επιπλέον βήματα που προστέθηκαν στο Power Query, εξαλείφετε τα περίεργα επίθημα στις διπλές επικεφαλίδες Q1 Q2 Q3 Q4.
  • Ο John χρησιμοποίησε μια στήλη ευρετηρίου νωρίς που θα χρησιμοποιηθεί στο τέλος για ταξινόμηση. Όμως - ο John συνένωσε τη στήλη ευρετηρίου του μετά την περιγραφή της κατηγορίας. Χρησιμοποίησε κάθετο χαρακτήρα σωλήνα | έτσι θα μπορούσε να σπάσει τα δεδομένα αργότερα.
  • Ο John πληκτρολόγησε τη στήλη υπό όρους ως προσαρμοσμένη στήλη αντί να χρησιμοποιεί τη διεπαφή υπό όρους στήλης.
Υπό όρους στήλη ως προσαρμοσμένη στήλη

Παρακολουθήστε το βίντεο του John εδώ:
https://www.youtube.com/watch?v=Dqmb6SEJDXI

Το Excel MVP Ken Puls, συν-συγγραφέας του M είναι για το βιβλίο (Data) Monkey που αποστέλλεται σε τρεις λύσεις. Η στήλη υπό όρους είναι πιθανώς η μικρότερη.

Αλλά η προτιμώμενη λύση του Ken αγνοεί την αρχική ερώτηση. Αντί να δημιουργεί τον πίνακα στο Power Query, δημιουργεί ένα σύνολο περιστρεφόμενων δεδομένων στο Power Query και στη συνέχεια τελειώνει με έναν συγκεντρωτικό πίνακα.

Η τελική προεπισκόπηση του Ken στο Power Query μοιάζει με αυτό:

Συγκεντρώσιμο σύνολο δεδομένων

Εδώ είναι ο κωδικός του Ken:

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 Hopkins είναι εδώ: Power Query: Αντιμετώπιση πολλαπλών πανομοιότυπων κεφαλίδων.
  • Ο κωδικός του Mike Girvin είναι εδώ: Power Query: Εξαγωγή αριστερών 2 χαρακτήρων από μια στήλη.
  • Η λύση τύπου του Roger Govier είναι εδώ: Formula Solutions.

Επιστρέψτε στην κύρια σελίδα για την πρόκληση Podcast 2316.

Διαβάστε το επόμενο άρθρο σε αυτήν τη σειρά: Power Query: Beyond the User Interface: Table.Split and More.

ενδιαφέροντα άρθρα...