Σημείωση
Αυτό είναι ένα από μια σειρά άρθρων που περιγράφουν λεπτομερώς τις λύσεις που αποστέλλονται για την πρόκληση Podcast 2316.
Ένα από τα προβλήματα με τη λύση μου είναι ότι η τελική ακολουθία των κατηγοριών δεν ταιριάζει απαραίτητα με την αρχική ακολουθία των στηλών. Αυτό το συνειδητοποίησα στο τέλος του βίντεό μου και επειδή δεν ήταν ιδιαίτερα σημαντικό, δεν ανησυχούσα για αυτό.
Ωστόσο, ο Josh Johnson έστειλε μια λύση που την αντιμετώπισε. Όταν ο Josh είπε ότι χρησιμοποίησε μια στήλη Index, υποθέτω ότι ήταν σαν το Index και το Modulo στο Power Query: Number Groups of Records ως 1 έως 5 επανειλημμένα. Αλλά η χρήση του Josh ήταν εντελώς διαφορετική.
Σημείωση: Το Excel MVP John MacDougall χρησιμοποίησε επίσης αυτήν τη μέθοδο, αλλά συνέδεσε τη στήλη ευρετηρίου με το τέλος της περιγραφής κατηγορίας. Δείτε το βίντεο του John εδώ: https://www.youtube.com/watch?v=Dqmb6SEJDXI και διαβάστε περισσότερα σχετικά με τον κωδικό του εδώ: Excel MVPs Επίθεση του προβλήματος εκκαθάρισης δεδομένων στο Power Query.
Νωρίς στη διαδικασία, όταν ο Josh είχε μόνο έξι δίσκους, πρόσθεσε ένα ευρετήριο ξεκινώντας από το 1. Ο Josh έκανε κλικ στη γραμμή τύπων και μετονόμασε τη στήλη Index ως Category.
Η στήλη Κατηγορία ήταν η νέα τελευταία στήλη. Χρησιμοποίησε το Move, για να αρχίσει να το μετακινεί στο πρώτο:
Μετά από αυτό, συμβαίνουν πολλά άλλα βήματα. Είναι βήματα που είναι καινοτόμα αλλά έχουν καλυφθεί ως επί το πλείστον από τα άλλα άρθρα μέχρι στιγμής. Μετά από πολλά τέτοια βήματα, άρχισα να σκέφτομαι ότι οι αριθμοί κατηγορίας 1 έως 6 ήταν απλώς ένα λάθος. Νόμιζα ότι πιθανότατα ο Josh θα τα διαγράψει χωρίς να τα χρησιμοποιήσει.
Josh Unpivots, στη συνέχεια υπό όρους στήλη, στη συνέχεια συμπληρώστε και, στη συνέχεια, περιστροφή, προσθέτει το σύνολο. Δεν φαίνεται να χρησιμοποιεί τη στήλη Κατηγορία. Μετά από πολλά βήματα, είναι εδώ:
Στη συνέχεια, όμως, στα τελικά βήματα, ο Josh ταξινομεί τα δεδομένα με βάση το όνομα υπαλλήλου και μετά την κατηγορία!
Σε αυτό το σημείο, μπορεί να διαγράψει τη στήλη Κατηγορία. Η τελική διαφορά: Το PTO έρχεται πριν από το Project A, όπως είχε στις αρχικές στήλες. Είναι μια ωραία πινελιά.
Θα επισημάνω επίσης ότι ο Josh έστειλε ένα βίντεο του που περνάει από αυτά τα βήματα. Kudos to Josh για χρήση συντομεύσεων πληκτρολογίου στο Power Query!
Εδώ είναι ο κωδικός του Josh:
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))), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Category", 1, 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",("Category", "Category Description", "Dept. Total", "Q1", "Q2", "Q3", "Q4", "Employee 1", "Q1_1", "Q2_2", "Q3_3", "Q4_4", "Employee 2", "Q1_5", "Q2_6", "Q3_7", "Q4_8", "Employee 3", "Q1_9", "Q2_10", "Q3_11", "Q4_12", "Employee 4", "Q1_13", "Q2_14", "Q3_15", "Q4_16")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category", "Category Description"), "Attribute", "Value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", (("Attribute", each Text.BeforeDelimiter(_, "_"), type text))), #"Added Conditional Column" = Table.AddColumn(#"Extracted Text Before Delimiter", "Employee Name", each if not Text.StartsWith((Attribute), "Q") then (Attribute) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee Name")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Attribute) = "Q1" or (Attribute) = "Q2" or (Attribute) = "Q3" or (Attribute) = "Q4") and ((Employee Name) "Dept. Total")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"(Attribute)), "Attribute", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Sorted Rows" = Table.Sort(#"Inserted Sum",(("Employee Name", Order.Ascending), ("Category", Order.Ascending))), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",("Category")) in #"Removed Columns"Επιστρέψτε στην κύρια σελίδα για την πρόκληση Podcast 2316.
Διαβάστε το επόμενο άρθρο σε αυτήν τη σειρά: Excel MVPs Attack the Data Cleansing Problem στο Power Query.
