...

So, all of this would take around 20 mins to do by hand every week. With Power Query it takes about 1 minute, and that's just to paste in the data from the downloaded spreadsheet, and run the query. 

Sample Data

I'm using the Sample Data I found for a temporal data set of employees. This is a data set that contains the full history of employee data over time. In this scenario we want to bring the employees in as contacts (yes, I know, that might not be what you would do in real life), and the old historic data will stay in the old system (again, may not happen in real life). 

Image Added

The sample data - we can see that the start and end date appear for every data point. We will simplify this data to produce just a list of employees, their departments. 

Steps

• Open the Power Query Ribbon
• Click From Table
• First we want to get rid of the data we don't need - so all the tend and start columns
• Just go right ahead and delete the columns - don't be afraid, you won't damage anything!
  • Right Click on the column heading > Remove. 
• That looks better already
• Now see the side panel to see what it has done. 
  • It's a good idea to rename Removed Columns to something more descriptive. 
  • eg Removed date columns
• Now Change M and F to Male and Female
  • Right click on the sex column 
    • Replace Values
    • Value to Find = M
    • Replace with = Male
    • Match entire cell contents = true
  • Oops, I made a mistake - I typed replace M with F. Ok that's easy. 
    • Just click the X next to Replaced Value in the panel and start again
  • Oops, another mistake - I typed Femals rater than Female. Easy. 
    
    • Just click the cog next to the action in the side panel and make the adjustment
  • Again rename your labels in the side panel
  • Now, if you have more than a few values you won't use find ad replace. 
• Notice how some steps get combined (eg all the deletions) and some steps get added as each one (eg the replacements).
• At this stage look at the advanced editor so you can see what is happening
  • Click Advanced Editor in the ribbon. 
  • You can edit this, but beware it does get confusing with a big data set. 
• Now, let's format the dates
  • The date format depends on which data loader you use to do the Import.
  • We will use MM\DD\YYYY  
• Right click on birthdate
  • Choose Change Type > Date > By Locale
  • Choose Date then English (United States)

Export

Again a real scenario. My client has KPIs on number of cases answered per month. They want to do a chart to present to management overlaying KPIs with the count of Cases for the year, by quarter. How can you do that in Salesforce. It is virtually impossible (Well, there may be a big hack way of doing it, but I have not found one. The new Charts from Apsona may be able to do it http://apsona.com/pages/sfdc/apsona-charting.html). 

...