Log in Try for free

Follow us

This website uses cookies

We use cookies to improve user experience. Choose which cookies you allow us to use. You can read more about our Cookie Policy in our privacy policy

See Cookies Details
Categories Corporate
Professional eBook

Excel: Get and Transform Data

by Anne Walsh
86
Language:  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!”
Professional Plus subscription free for the first 30 days, then $6.99/mo
Access this book on our eReader, no adverts inside the book
Description
Content

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
Write a review
About the Author
Anne

Anne Walsh

Discover Author
Related Content