Σημείωση
Αυτό είναι ένα από μια σειρά άρθρων που περιγράφουν λεπτομερώς τις λύσεις που αποστέλλονται για την πρόκληση Podcast 2316.
Στο αρχικό μου πρόβλημα διαμόρφωσης δεδομένων, αντιμετώπισα ένα πρόβλημα πολύ νωρίς στη διαδικασία. Τα εισερχόμενα δεδομένα θα είχαν πολλές στήλες με επικεφαλίδα του Q1.
Στη λύση μου, δημιούργησα ένα ονομαστικό εύρος "UglyData" και το εισήγαγα στο Power Query. Αυτό οδηγεί στο δυστυχισμένο αποτέλεσμα του Power Query που μετονομάζει τις στήλες μου σε Q1_1.
Αργότερα, μετά την αποσύνδεση, έπρεπε να εξαγάγω μόνο τους δύο αριστερούς χαρακτήρες από αυτές τις κεφαλίδες.
Υπήρχαν τρεις ξεχωριστές λύσεις σε αυτό το πρόβλημα:
- Wyn Hopkins και Demote Headers
- MF Wong και αποεπιλέξτε το My Table Has Headers (προτείνει επίσης ο Peter Bartholomew)
- Jason M και απλώς διαγράψτε τις Προωθούμενες κεφαλίδες (προτείνονται επίσης από τους Ondřej Malinský και Excel MVP John MacDougall)
Η πρώτη καινοτομία ήταν από την Wyn Hopkins στο Access Analytic. Αντί για μια ονομαστική περιοχή, η Wyn μετέτρεψε τα δεδομένα σε έναν πίνακα χρησιμοποιώντας Ctrl + T. Σε αυτό το σημείο, η ζημιά στις επικεφαλίδες έγινε, καθώς το Excel μετέτρεψε τις επικεφαλίδες σε:
Μόλις ο Wyn πήρε τα δεδομένα στο Power Query, τότε άνοιξε το αναπτυσσόμενο μενού Use First Row as Headers και επέλεξε το Use Headers ως First Row. Δεν κατάλαβα ποτέ ότι ήταν εκεί. Δημιουργεί ένα βήμα που ονομάζεται Table.DemoteHeaders.
Αλλά, ακόμη και με τη βελτίωση του Wyn, θα έπρεπε αργότερα να εξαγάγει τους πρώτους 2 χαρακτήρες από αυτές τις κεφαλίδες.
Η δεύτερη καινοτομία είναι η τεχνική του MF Wong. Όταν δημιούργησε τον πίνακα, απενεργοποίησε το My Table Has Headers!
Αυτό διασφαλίζει ότι το Excel αφήνει τις πολλαπλές κεφαλίδες Q1 μόνες και δεν χρειάζεται να εξαγάγετε το επιπλέον επίθημα αργότερα.
Κατανοώ ότι υπάρχουν άνθρωποι στο στρατόπεδο «Λατρεύω τραπέζια». Το βίντεο του MF Wong έδειξε πώς θα μπορούσε να προσθέσει νέους υπαλλήλους στα δεξιά των δεδομένων και ο πίνακας επεκτείνεται αυτόματα. Υπάρχουν πολλοί καλοί λόγοι για τη χρήση πινάκων.
Αλλά, επειδή μου αρέσουν τα αθροίσματα, οι προσαρμοσμένες προβολές και το φίλτρο κατά επιλογή, τείνω να μην χρησιμοποιώ πίνακες. Έτσι, εκτιμώ τη λύση του Jason M. Διατήρησε τα δεδομένα ως το ονομαζόμενο εύρος των UglyData. Μόλις εισήγαγε τα δεδομένα στο Power Query, διέγραψε αυτά τα δύο βήματα:
Τώρα, με τα δεδομένα απλά στη σειρά 1, δεν υπάρχει καμία ταλαιπωρία με πολλές στήλες που ονομάζονται Q1.
Εδώ είναι ο κώδικας του Wyn Hopkin που δείχνει DemotedHeaders:
let Source = Excel.CurrentWorkbook()((Name="Table1"))(Content), #"Demoted Headers1" = Table.DemoteHeaders(Source), #"Transposed Table1" = Table.Transpose(#"Demoted Headers1"), #"Added Custom" = Table.AddColumn(#"Transposed Table1", "Custom", each if Text.Start((Column1),1) = "Q" then null else (Column1)), #"Filled Down" = Table.FillDown(#"Added Custom",("Custom")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Custom) "Dept. Total")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith((Column1), "Employee")), #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", (PromoteAllScalars=true)), #"Extracted First Characters" = Table.TransformColumns(#"Promoted Headers", (("Category Description", each Text.Start(_, 2), type text))), #"Reordered Columns" = Table.ReorderColumns(#"Extracted First Characters",("Category Description_1", "Category Description", "Administrative", "Holiday", "PTO/LOA/Jury Duty", "Project A", "Project B", "Project C")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category Description_1", "Category Description"), "Attribute", "Value"), #"Reordered Columns1" = Table.ReorderColumns(#"Unpivoted Other Columns",("Category Description_1", "Attribute", "Category Description", "Value")), #"Pivoted Column" = Table.Pivot(#"Reordered Columns1", List.Distinct(#"Reordered Columns1"(#"Category Description")), "Category Description", "Value", List.Sum), #"Reordered Columns2" = Table.ReorderColumns(#"Pivoted Column",("Attribute", "Category Description_1", "Q1", "Q2", "Q3", "Q4")), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns2",(("Attribute", "Cat Deasc"), ("Category Description_1", "Emp Name"))), #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",(("Emp Name", type text), ("Q1", Int64.Type), ("Q2", Int64.Type), ("Q3", Int64.Type), ("Q4", Int64.Type))), #"Inserted Sum" = Table.AddColumn(#"Changed Type", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), Int64.Type) in #"Inserted Sum"Επιστρέψτε στην κύρια σελίδα για την πρόκληση Podcast 2316.
Διαβάστε το επόμενο άρθρο σε αυτήν τη σειρά: Power Query: Διαγραφή αυτού, Διαγραφή αυτών ή διαγραφή τίποτα ;.
