Δημιουργία τύπων του Power Query στο Excel

Η πρόγραμμα επεξεργασίας Power Query παρέχει ένα ερώτημα δεδομένων και μια εμπειρία διαμόρφωσης για το Excel, την οποία μπορείτε να χρησιμοποιήσετε για να αναδιαμορφώσετε δεδομένα από πολλές προελεύσεις δεδομένων. Για να εμφανίσετε το παράθυρο πρόγραμμα επεξεργασίας Power Query, εισαγάγετε δεδομένα από εξωτερικές προελεύσεις δεδομένωνσε ένα φύλλο εργασίας του Excel, επιλέξτε ένα κελί στα δεδομένα και, στη συνέχεια, επιλέξτε Ερώτημα > Επεξεργασία. Ακολουθεί μια σύνοψη των κύριων στοιχείων.

1. Η κορδέλα πρόγραμμα επεξεργασίας Power Query που χρησιμοποιείτε για να διαμορφώσετε τα δεδομένα σας

2. Το παράθυρο "Ερωτήματα" που χρησιμοποιείτε για να εντοπίσετε προελεύσεις δεδομένων και πίνακες

3. Μενού περιβάλλοντος που αποτελούν βολικές συντομεύσεις για εντολές στην κορδέλα

4. Η Προεπισκόπηση δεδομένων που εμφανίζει τα αποτελέσματα των βημάτων που εφαρμόζονται στα δεδομένα

5. Το παράθυρο "Ρυθμίσεις ερωτήματος" που παραθέτει ιδιότητες και κάθε βήμα του ερωτήματος

Στο παρασκήνιο, κάθε βήμα σε ένα ερώτημα βασίζεται σε έναν τύπο που είναι ορατός στη γραμμή τύπων.

Ενδέχεται να υπάρχουν φορές που θέλετε να τροποποιήσετε ή να δημιουργήσετε έναν τύπο. Οι τύποι χρησιμοποιούν το Power Query γλώσσας τύπων, την οποία μπορείτε να χρησιμοποιήσετε για να δημιουργήσετε απλές και σύνθετες παραστάσεις. Για περισσότερες πληροφορίες σχετικά με τη σύνταξη, τα ορίσματα, τις παρατηρήσεις, τις συναρτήσεις και παραδείγματα, ανατρέξτε στο θέμα Power Query γλώσσα τύπου M.

Χρησιμοποιώντας ως παράδειγμα μια λίστα πρωταθλήματος ποδοσφαίρου, χρησιμοποιήστε Power Query για να λάβετε ανεπεξέργαστα δεδομένα που βρήκατε σε μια τοποθεσία Web και να τα μετατρέψετε σε έναν καλά μορφοποιημένο πίνακα. Παρακολουθήστε πώς δημιουργούνται τα βήματα ερωτήματος και οι αντίστοιχοι τύποι για κάθε εργασία στο παράθυρο Ρυθμίσεις ερωτήματος στην περιοχή Εφαρμοσμένα βήματα και στη γραμμή τύπων.

Διαδικασία

1. Για να εισαγάγετε τα δεδομένα, επιλέξτε Δεδομένα > Από το Web, πληκτρολογήστε "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" στο πλαίσιο Διεύθυνση URL και, στη συνέχεια, επιλέξτε OK.

2. Στο παράθυρο διαλόγου Περιήγηση , επιλέξτε τον πίνακα Αποτελέσματα [Επεξεργασία] στα αριστερά και, στη συνέχεια, επιλέξτε Μετασχηματισμός δεδομένων στο κάτω μέρος. Εμφανίζεται το πρόγραμμα επεξεργασίας Power Query.

3. Για να αλλάξετε το προεπιλεγμένο όνομα ερωτήματος, στο παράθυρο Ρυθμίσεις ερωτήματος , στην περιοχή Ιδιότητες, διαγράψτε τη φράση "Αποτελέσματα [Επεξεργασία]" και, στη συνέχεια, πληκτρολογήστε "Πρωταθλητές UEFA".

4. Για να καταργήσετε ανεπιθύμητες στήλες, επιλέξτε την πρώτη, την τέταρτη και την πέμπτη στήλη και, στη συνέχεια, επιλέξτε Κεντρική > Κατάργηση στήλης > Κατάργηση άλλων στηλών.

5. Για να καταργήσετε ανεπιθύμητες τιμές, επιλέξτε Στήλη1, επιλέξτε Κεντρική > Αντικατάσταση τιμών, πληκτρολογήστε "λεπτομέρειες" στο πλαίσιο Τιμές για εύρεση και, στη συνέχεια, επιλέξτε OK.

6. Για να καταργήσετε γραμμές που περιέχουν τη λέξη "Έτος", επιλέξτε το βέλος φίλτρου στη Στήλη1, καταργήστε την επιλογή από το πλαίσιο ελέγχου δίπλα στην επιλογή "Έτος" και, στη συνέχεια, επιλέξτε OK.

7. Για να μετονομάσετε τις κεφαλίδες στηλών, κάντε διπλό κλικ σε κάθε μία από αυτές και, στη συνέχεια, αλλάξτε τη "Στήλη1" σε "Έτος", "Στήλη4" σε "Νικητής" και "Στήλη5" σε "Τελική βαθμολογία".

8. Για να αποθηκεύσετε το ερώτημα, επιλέξτε Κεντρική > Κλείσιμο & Φόρτωση.

Result

Ο παρακάτω πίνακας είναι μια σύνοψη κάθε εφαρμοσμένου βήματος και του αντίστοιχου τύπου.

Βήμα και εργασία ερωτήματος	Τύπος
Προέλευση Σύνδεση με προέλευση δεδομένων web	= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship"))
Περιήγηση Επιλέξτε τον πίνακα για σύνδεση	=Source{2}[Data]
Αλλαγμένος τύπος Αλλαγή τύπων δεδομένων (που Power Query αυτόματα)	= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}})
Καταργήθηκε άλλες στήλες Κατάργηση άλλων στηλών για να εμφανίζονται μόνο οι στήλες που σας ενδιαφέρουν	= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"})
Τιμή αντικατάστασης Αντικατάσταση τιμών για την εκκαθάριση τιμών σε μια επιλεγμένη στήλη	= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"})
Φιλτραρισμένες γραμμές Φιλτράρισμα τιμών σε μια στήλη	= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))
Στήλες που έχουν μετονομαστεί Αλλάξτε τις κεφαλίδες των στηλών ώστε να έχουν νόημα	= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Σημαντικό    Να είστε προσεκτικοί κατά την επεξεργασία των βημάτων Προέλευση, Περιήγηση  και Αλλαγμένος τύπος, επειδή δημιουργούνται από Power Query για τον ορισμό και τη ρύθμιση της προέλευσης δεδομένων.

Εμφάνιση ή απόκρυψη της γραμμής τύπων

Η γραμμή τύπων εμφανίζεται από προεπιλογή, αλλά εάν δεν είναι ορατή, μπορείτε να την επανεμφανίζετε.

• Επιλέξτε Προβολή > διάταξη > γραμμή τύπων.

Eit a formula in the formula bar

1. Για να ανοίξετε ένα ερώτημα, εντοπίστε ένα που έχει φορτωθεί προηγουμένως από το πρόγραμμα επεξεργασίας Power Query, επιλέξτε ένα κελί στα δεδομένα και, στη συνέχεια, επιλέξτε Ερώτημα > Επεξεργασία. Για περισσότερες πληροφορίες , ανατρέξτε στο θέμα Δημιουργία, φόρτωση ή επεξεργασία ερωτήματος στο Excel.

2. Στο παράθυρο Ρυθμίσεις ερωτήματος , στην περιοχή Εφαρμοσμένα βήματα, επιλέξτε το βήμα που θέλετε να επεξεργαστείτε.

3. Στη γραμμή τύπων, εντοπίστε και αλλάξτε τις τιμές των παραμέτρων και, στη συνέχεια, επιλέξτε το εικονίδιο Enter ή πατήστε το πλήκτρο Enter. Για παράδειγμα, αλλάξτε αυτόν τον τύπο για να διατηρήσετε επίσης τη στήλη2:
   
   Πριν: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   μετά:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Επιλέξτε το εικονίδιο Enter ή πατήστε το πλήκτρο Enter για να δείτε τα νέα αποτελέσματα να εμφανίζονται στην Προεπισκόπηση δεδομένων.

5. Για να δείτε το αποτέλεσμα σε ένα φύλλο εργασίας του Excel, επιλέξτε Κεντρική > Κλείσιμο & Φόρτωση.

Δημιουργία τύπου στη γραμμή τύπων

Για ένα απλό παράδειγμα τύπου, ας μετατρέψουμε μια τιμή κειμένου σε σωστή γραφή χρησιμοποιώντας τη συνάρτηση Text.Proper.

1. Για να ανοίξετε ένα κενό ερώτημα, στο Excel επιλέξτε Δεδομένα > Λήψη δεδομένων > Από άλλες προελεύσεις > Κενό ερώτημα. Για περισσότερες πληροφορίες , ανατρέξτε στο θέμα Δημιουργία, φόρτωση ή επεξεργασία ερωτήματος στο Excel.

2. Στη γραμμή τύπων, πληκτρολογήστε=Text.Proper("text value")και, στη συνέχεια, επιλέξτε το εικονίδιο Enter ή πατήστε το πλήκτρο Enter.
   
   Τα αποτελέσματα εμφανίζονται στην Προεπισκόπηση δεδομένων.

3. Για να δείτε το αποτέλεσμα σε ένα φύλλο εργασίας του Excel, επιλέξτε Κεντρική > Κλείσιμο & Φόρτωση.

Αποτέλεσμα:

 Όταν δημιουργείτε έναν τύπο, Power Query επικυρώνει τη σύνταξη του τύπου. Ωστόσο, κατά την εισαγωγή, την αναδιάταξη ή τη διαγραφή ενός ενδιάμεσου βήματος σε ένα ερώτημα, ενδέχεται να διακόψετε ένα ερώτημα.  Να επαληθεύετε πάντα τα αποτελέσματα στην Προεπισκόπηση δεδομένων.

Σημαντικό    Να είστε προσεκτικοί κατά την επεξεργασία των βημάτων Προέλευση, Περιήγηση  και Αλλαγμένος τύπος, επειδή δημιουργούνται από Power Query για τον ορισμό και τη ρύθμιση της προέλευσης δεδομένων.

Επεξεργασία τύπου με χρήση ενός παραθύρου διαλόγου

Αυτή η μέθοδος χρησιμοποιεί παράθυρα διαλόγου που ποικίλλουν ανάλογα με το βήμα. Δεν χρειάζεται να γνωρίζετε τη σύνταξη του τύπου.

1. Για να ανοίξετε ένα ερώτημα, εντοπίστε ένα που έχει φορτωθεί προηγουμένως από το πρόγραμμα επεξεργασίας Power Query, επιλέξτε ένα κελί στα δεδομένα και, στη συνέχεια, επιλέξτε Ερώτημα > Επεξεργασία. Για περισσότερες πληροφορίες , ανατρέξτε στο θέμα Δημιουργία, φόρτωση ή επεξεργασία ερωτήματος στο Excel.

2. Στο παράθυρο Ρυθμίσεις ερωτήματος , στην περιοχή Εφαρμοσμένα βήματα, επιλέξτε το εικονίδιο Επεξεργασία ρυθμίσεων του βήματος που θέλετε να επεξεργαστείτε ή κάντε δεξί κλικ στο βήμα και, στη συνέχεια, επιλέξτε Επεξεργασία ρυθμίσεων.

3. Στο παράθυρο διαλόγου, κάντε τις αλλαγές σας και, στη συνέχεια, επιλέξτε OK.

Εισαγωγή βήματος

Αφού ολοκληρώσετε ένα βήμα ερωτήματος που αναδιαμορφώνει τα δεδομένα σας, προστίθεται ένα βήμα ερωτήματος κάτω από το τρέχον βήμα ερωτήματος. Ωστόσο, όταν εισάγετε ένα βήμα ερωτήματος στη μέση των βημάτων, ενδέχεται να παρουσιαστεί σφάλμα στα επόμενα βήματα. Power Query εμφανίζει μια προειδοποίηση "Εισαγωγή βήματος" όταν προσπαθείτε να εισαγάγετε ένα νέο βήμα και το νέο βήμα τροποποιεί πεδία, όπως ονόματα στηλών, που χρησιμοποιούνται σε οποιοδήποτε από τα βήματα που ακολουθούν το βήμα που έχει εισαχθεί.

1. Στο παράθυρο Ρυθμίσεις ερωτήματος , στην περιοχή Εφαρμοσμένα βήματα, επιλέξτε το βήμα που θέλετε να προηγείται αμέσως του νέου βήματος και του αντίστοιχου τύπου.

2. Επιλέξτε το εικονίδιο Προσθήκη βήματος στα αριστερά της γραμμής τύπων. Εναλλακτικά, κάντε δεξί κλικ σε ένα βήμα και, στη συνέχεια, επιλέξτε Εισαγωγή βήματος μετά. Δημιουργείται ένας νέος τύπος με τη μορφή :
   
   = <nameOfTheStepToReference>, όπως =Production.WorkOrder.

3. Πληκτρολογήστε τον νέο τύπο χρησιμοποιώντας τη μορφή:
   
   =Class.Function(ReferenceStep[,otherparameters])
   
   Για παράδειγμα, ας υποθέσουμε ότι έχετε έναν πίνακα με τη στήλη Gender και θέλετε να προσθέσετε μια στήλη με την τιμή "Ms". ή "mr.", ανάλογα με το φύλο του ατόμου. Ο τύπος θα ήταν:
   
   =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Αναδιάταξη βήματος

• Στο παράθυρο Ρυθμίσεις ερωτημάτων στην περιοχή Εφαρμοσμένα βήματα, κάντε δεξί κλικ στο βήμα και, στη συνέχεια, επιλέξτε Μετακίνηση επάνω ή Μετακίνηση κάτω.

Διαγραφή βήματος

• Επιλέξτε το εικονίδιο Διαγραφή στα αριστερά του βήματος ή κάντε δεξί κλικ στο βήμα και, στη συνέχεια, επιλέξτε Διαγραφή ή Διαγραφή μέχρι το τέλος. Το εικονίδιο Διαγραφή είναι επίσης διαθέσιμο στα αριστερά της γραμμής τύπων.

Σε αυτό το παράδειγμα, ας μετατρέψουμε το κείμενο μιας στήλης σε σωστή γραφή χρησιμοποιώντας ένα συνδυασμό τύπων στο Προηγμένο πρόγραμμα επεξεργασίας. 

Για παράδειγμα, έχετε έναν πίνακα του Excel, που ονομάζεται Orders, με μια στήλη ProductName που θέλετε να μετατρέψετε σε σωστή γραφή. 

Πριν:

Μετά:

Όταν δημιουργείτε ένα σύνθετο ερώτημα, δημιουργείτε μια σειρά βημάτων τύπου ερωτήματος με βάση την παράσταση let. Χρησιμοποιήστε την παράσταση let για να αντιστοιχίσετε ονόματα και να υπολογίσετε τιμές που, στη συνέχεια, αναφέρονται από τον όρο in , ο οποίος ορίζει το βήμα. Αυτό το παράδειγμα επιστρέφει το ίδιο αποτέλεσμα με αυτό της ενότητας "Δημιουργία τύπου στη γραμμή τύπων".

let  
    Source = Text.Proper("hello world")
in  
    Source  

Θα δείτε ότι κάθε βήμα βασίζεται σε ένα προηγούμενο βήμα κάνοντας αναφορά σε ένα βήμα προς όνομα. Ως υπενθύμιση, η Power Query γλώσσας τύπου κάνει διάκριση πεζών-κεφαλαίων.

Φάση 1: Άνοιγμα του Προηγμένο πρόγραμμα επεξεργασίας

1. Στο Excel, επιλέξτε Δεδομένα > Λήψη δεδομένων > Άλλες προελεύσεις > Κενό ερώτημα. Για περισσότερες πληροφορίες , ανατρέξτε στο θέμα Δημιουργία, φόρτωση ή επεξεργασία ερωτήματος στο Excel.

2. Στο πρόγραμμα επεξεργασίας Power Query, επιλέξτε Κεντρική > Προηγμένο πρόγραμμα επεξεργασίας, η οποία ανοίγει με ένα πρότυπο της παράστασης let.

Φάση 2: Ορισμός της προέλευσης δεδομένων

1. Δημιουργήστε την παράσταση let χρησιμοποιώντας τη συνάρτηση Excel.CurrentWorkbook ως εξής:
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
   in
       
   
   Source#x4

2. Για να φορτώσετε το ερώτημα σε ένα φύλλο εργασίας, επιλέξτε Τέλος και, στη συνέχεια, επιλέξτε Κεντρική > Κλείσιμο & Φόρτωση > Κλείσιμο & Φόρτωση.

Αποτέλεσμα:

Φάση 3: Προβιβασμός της πρώτης γραμμής σε κεφαλίδες

1. Για να ανοίξετε το ερώτημα, από το φύλλο εργασίας επιλέξτε ένα κελί στα δεδομένα και, στη συνέχεια, επιλέξτε Ερώτημα > Επεξεργασία. Για περισσότερες πληροφορίες, ανατρέξτε στο θέμα Δημιουργία, φόρτωση ή επεξεργασία ερωτήματος στο Excel (Power Query).

2. Στο πρόγραμμα επεξεργασίας Power Query, επιλέξτε Κεντρική > Προηγμένο πρόγραμμα επεξεργασίας, η οποία ανοίγει με την πρόταση που δημιουργήσατε στη Φάση 2: Ορισμός της προέλευσης δεδομένων.

3. Στην παράσταση let, προσθέστε τα στοιχεία #"First Row as Header" και Table.PromoteHeaders ως εξής:
   
   let
       
   Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],   
       #"First Row as Header"#x3#"First Row as Header" = Table.PromoteHeaders(Source)
   

4. Για να φορτώσετε το ερώτημα σε ένα φύλλο εργασίας, επιλέξτε Τέλος και, στη συνέχεια, επιλέξτε Κεντρική > Κλείσιμο & Φόρτωση > Κλείσιμο & Φόρτωση.

Αποτέλεσμα:

Φάση 4: Μετατροπή κάθε τιμής σε μια στήλη σε σωστή γραφή

1. Για να ανοίξετε το ερώτημα, από το φύλλο εργασίας επιλέξτε ένα κελί στα δεδομένα και, στη συνέχεια, επιλέξτε Ερώτημα > Επεξεργασία. Για περισσότερες πληροφορίες , ανατρέξτε στο θέμα Δημιουργία, φόρτωση ή επεξεργασία ερωτήματος στο Excel.

2. Στο πρόγραμμα επεξεργασίας Power Query, επιλέξτε Κεντρική > Προηγμένο πρόγραμμα επεξεργασίας, η οποία ανοίγει με την πρόταση που δημιουργήσατε στη Φάση 3: Προβιβασμός της πρώτης γραμμής σε κεφαλίδες.

3. Στην παράσταση let, μετατρέψτε κάθε τιμή στήλης ProductName σε κατάλληλο κείμενο χρησιμοποιώντας τη συνάρτηση Table.TransformColumns, αναφερόμενη στο προηγούμενο βήμα τύπου ερωτήματος "First Row as Header", προσθέτοντας #"Capitalized Each Word" στην προέλευση δεδομένων και, στη συνέχεια, αντιστοιχίζοντας το αποτέλεσμα #"Capitalized Each Word" στο αποτέλεσμα in.
   
   let
       Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
       #"First Row as Header" = Table.PromoteHeaders(Source),
       #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
   in
       #"Capitalized Each Word"

4. Για να φορτώσετε το ερώτημα σε ένα φύλλο εργασίας, επιλέξτε Τέλος και, στη συνέχεια, επιλέξτε Κεντρική > Κλείσιμο & Φόρτωση > Κλείσιμο & Φόρτωση.

Αποτέλεσμα:

Μπορείτε να ελέγξετε τη συμπεριφορά της γραμμής τύπων στο πρόγραμμα επεξεργασίας Power Query για όλα τα βιβλία εργασίας σας.

Εμφάνιση ή απόκρυψη της γραμμής τύπων

1. Επιλέξτε Επιλογές> αρχείου και Ρυθμίσεις > Επιλογές ερωτήματος.

2. Στο αριστερό τμήμα παραθύρου, στην περιοχή ΚΑΘΟΛΙΚΟ, επιλέξτε πρόγραμμα επεξεργασίας Power Query.

3. Στο δεξιό τμήμα παραθύρου, στην περιοχή Διάταξη, επιλέξτε ή καταργήστε την επιλογή Εμφάνιση της γραμμής τύπων.

Ενεργοποίηση ή απενεργοποίηση του M Intellisense

1. Επιλέξτε Επιλογές> αρχείου και Ρυθμίσεις > Επιλογές ερωτήματος .

2. Στο αριστερό τμήμα παραθύρου, στην περιοχή ΚΑΘΟΛΙΚΟ, επιλέξτε πρόγραμμα επεξεργασίας Power Query.

3. Στο δεξιό τμήμα παραθύρου, στην περιοχή Τύπος, επιλέξτε ή καταργήστε την επιλογή ενεργοποίησης του M Intellisense στη γραμμή τύπων, το πρόγραμμα επεξεργασίας για προχωρημένους και το παράθυρο διαλόγου προσαρμοσμένης στήλης.

Σημείωση    Η αλλαγή αυτής της ρύθμισης θα τεθεί σε ισχύ την επόμενη φορά που θα ανοίξετε το παράθυρο πρόγραμμα επεξεργασίας Power Query.