Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Next »

Excel Power Query is amazing, and powerful, and a fabulous tool for anyone serious about getting data in or out of Salesforce. 

Unfortunately if you use a OSx you are out of luck. Try installing windows in a VM just to get Power Query goodness. 

I will go through a few scenarios with examples and screen shots of how to use Power Query simply. 

There is so much you can do with Power Query, and it's related product Power BI, but I am only scratching the surface.

Download

Power Query is a free download from Microsoft for Office 2010 and 2013 running on Windows 7 plus. I am running Office 2010 on Windows 7 64 bit at the moment and it is working great. 

Download it here https://www.microsoft.com/en-au/download/details.aspx?id=39379

What I love about Power Query

  • It's almost like my first programming love - Excel 4 macro language (XLM). This is the programming language I taught myself on, which is probably why I'm not much of a coder now. http://www.brighthub.com/computing/windows-platform/articles/84345.aspx
  • You just do things in the workbook and it records what you are doing
  • You can go back and edit earlier steps
  • You can rename steps to document as you go
  • You can insert new steps
  • You can do very powerful functions or very simple things. 
  • You can repeat and repeat over and over again without losing the source data. It is so cool!

Import

Here's the scenario. (This is a real scenario, but I'm using sample data to protect the innocent). 

I have a heap of data I need to import into Salesforce, and it is coming from another horrible database that has no data validation and has suffered years of neglect. The current situation is that they need to import the data into Salesforce on a weekly basis and import ALL the data because their database has no way of exporting just the new or updated records (I know, right??!?!? - People who work on these sort of databases need to reevaluate their life choices, I reckon).

The data horrors that need to be cleaned in this data set include:

  • Deleting unneeded columns
  • Extracting the year from the date field because the dates are not entered in a consistent way
  • Merging 3 columns into one separated by semicolons so they go into a multi select picklist. 
  • Making all the ID numbers the same length by prepending zeros
  • Data consistency eg Female entered as Female, female, f, F or fem.
  • V-Lookups to replace data
  • Combine the area code and the phone number into one field and attempt to format it nicely. 
  • Ensure all the states are entered as abbreviations rather than full words
  • Ensure Countries are entered as Australia and not Aus
  • Turn yes and no values into true and false (or 1 and 0)
  • Remove duplicate records
  • Remove test or dummy data (entered with an ID number beginning with Z - at least it is easy to find). 

Things you could also do:

  • Check for valid email addresses using regex
  • Try to turn phone numbers into international format (though this is difficult and would only be useful if you have a limited set of data - eg you know the majority of your entries are Australian).
  • Merge two lists into one. 
  • Separate out the data into separate import files for Accounts and Contacts. (I'm using Skyvva so I don't need to do that in this instance). 

 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. 

 

 

 

 

 

Export

 

  • No labels