Connexion Testez gratuitement

Follow us

Bienvenue sur Bookboon

Afin de vous proposer nos services, nous nous reposons sur des cookies. Ceux-ci sont essentiels pour accéder à nos fonctionnalités.

Lire les détails relatifs aux cookies
Catégories Corporatif
Livre électronique professionnel

Excel: Get and Transform Data

par Anne Walsh
86
Langue :  English
Anne Walsh is a freelance trainer (since the mid-90s) aka as The Excel Lady. She got this name when people would look at her and say, “you, you’re the Excel Lady!”
Abonnement Pro Plus gratuit pour les 30 premiers jours, puis $6.99/mois
Lisez ce livre avec notre eReader, sans pubs
Description
Contenu

If you spend a lot of your valuable time every month cleaning up data so your Excel formulas will actually work – you need to know about Power Query. It’s a free Excel add-in that can cut your data cleansing work from hours to minutes. You can rapidly clean up and re-organise your data, convert US to European dates and vice versa, unpivot your data so you can re-analyse it and much more. The files mentioned in this book can be downloaded here:https://s3-eu-west-1.amazonaws.com/the-excel-expert/PQBook_Book_Boon_files.zip

About the author

Anne Walsh is a freelance trainer (since the mid-90s) aka as The Excel Lady. She got this name when people would look at her and say, “you, you’re the Excel Lady!” She saw her first spreadsheet in the early 1990s and has been intrigued by them ever since, even though they only came in green or orange. She likes to say she puts the “fun in functions” and is excited by the potential of Power Query.

  • About the author
  1. Introduction - What is Power Query (AKA Get and Transform data)
    1. What do I mean by data cleansing?
    2. Excel versions it is available for
    3. Conclusion
  2. How do we get our data into Power Query?
    1. Get Data
    2. From Table/Range
    3. From Text/CSV
    4. From Web
    5. Recent Sources
    6. Existing connections
    7. Refresh All
    8. How do I actually get my data into Power Query?
    9. Pulling our data into Power Query (Get and Transform Data)
    10. Ribbon
    11. Query Settings
    12. Applied Steps
    13. Getting the data back into Excel
    14. What happens when I get new data?
    15. Conclusion
  3. Cleaning up data – general
    1. Making my first row headers
    2. Filter out blank rows (null)
    3. Deleting columns
    4. Moving columns
    5. Keep rows/remove rows
    6. Doing a Fill Down/Fill up
    7. Replacing Values
    8. Column from Example
    9. Conclusion
  4. Cleaning up text
    1. Formatting your data
    2. Capitalize each word
    3. Extract from text
    4. Conclusion
  5. Cleaning up Date and Time
    1. Extract age from dates automatically
    2. Calculate duration between two dates
    3. Calculate duration between two times
    4. Converting dates in mm/dd/yyyy format to dd/mm/yyyy format
    5. Extract Year/Month/Quarter/Week/Day information from dates
    6. Conclusion
  6. Unpivoting data
    1. Introduction - How to unpivot data and why it is important.
    2. Unpivot columns
    3. Unpivot other columns
    4. Conclusion
  7. How do I keep my queries up to date?
    1. Pulling in data from a folder
    2. Re-using your queries
    3. Conclusion
Ecrire un commentaire
A propos de l'auteur
Anne

Anne Walsh

Découvrir l'auteur
Contenu associé