...

• 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!
• You can run Salesforce reports right in Excel! Whenever you open up the spreadsheet there it is - your latest data * (caveats apply, as per usual). 

Uses

• When doing a major import into Salesforce, you will want to do more than one import - eg one into the sandbox, and one into live. The data will change in that time frame. Power Query allows you to clean up the data once, then when the new export is done just before go-live you can run the power query again to format the new data. 
• You can practice practice practice and get more and more sophisticated with your data cleansing
• You should be able to do a good dedupe before importing the main data. 

Import

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

...

• 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). 

 SoSo, 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

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). 

I ended up using http://www.klipfolio.com/ which is a brilliant brilliant, and cheap solution. But Klipfolio has a very high learning curve and is not for your average punter. 

I want to replicate this in Excel using Power Query. 

Related Articles

...