Excel: Get and Transform Data

Ratings :
( 0 )
86 pages
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!” She saw her first spreadsheet in th
This is a Business eBook
Free 30-day trial
Business subscription free for the first 30 days, then $5.99/mo
 
Buy the eBook :
Access this book on our eReader, no adverts inside the book
Latest eBooks
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...

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