Page Comparison

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

...

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 64bit at the moment and it is working great. 

...

...

Wait, where is this bi-directionality. Do we know anything about Salesforce for excel? 

What I love about Power Query

...

Wait, where is this bi-directionality? How? Why? Do we know anything about Salesforce for Excel yet? #askforce on twitter doesn't know about it, so it will be interesting to see. Hopefully it will overcome the 2000 row limit.  

Here is a very very basic Salesforce powerpoint presentation about Power Query - if you are into that sort of thing https://success.salesforce.com/06930000003vJfc 

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

...

• 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 and 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)
• Do the same for Hiredate
• Remove Salary since Salesforce is not our payroll system
• OK, things are looking pretty good now, it may be time to remove the duplicates. 
• You can remove the duplicates based on one or more columns https://support.office.com/en-nz/article/Remove-duplicates-Power-Query-d9cffc69-dc5d-4d94-8b66-72779688874d?ui=en-US&rs=en-NZ&ad=NZ
  • In our case we will right click on EmpNo. and choose remove Duplicates.
• At this stage we will close and load and see what we've got. 
• Oops! We have lost our Title and Department data. 
  • Right click on Table 1 in the side panel and click Edit
  • Remove the Removed Duplicates action
  • Phew! no data lost! 
• Now highlight empno deptno and title and choose remove duplicates. 
• Easy! Our data now looks like this

...

As this is a simple vlookup we are going to use the merge function https://support.office.com/en-nz/article/Merge-queries-Power-Query-fd157620-5470-4c0f-b132-7ca2616d17f9?ui=en-US&rs=en-NZ&ad=NZ but see the articles below for a vlookup on steroids example. 

...

• Of course you can only use this if you have the APIs enabled (from Professional Edition, if you pay, up to Enterprise, Developer and Ultimate). 
• Beware of security. DO NOT LOG INTO EXCEL AS AN ADMINISTRATOR! All of your data is then visible in Excel by anyone who opens up your PC. Use an Integration account that specifically only has read only access to the data you need it to have access to. 
• The user remains logged in when you close excel, so it is logged in next time you open excel. 
• Attachment object is not supported. 
• Of course, this is one of my biggest issues - it keeps people reliant on Excel rather than trusting and using the data in Salesforce, this is one of my biggest issues - it keeps people reliant on Excel rather than trusting and using the data in Salesforce. 
• You can't use Matrix Reports. 
• Oh No! You can't log into Salesforce from Excel Power Query into a Partner Community - it's a stupid limitation in Excel Power Query where it requires Salesforce in the URL rather than just force.com (I was only testing it on a sandbox - hopefully it would be OK in Prod). And yes, the user has use API access. 

Next Steps

Please comment below as to how far you have taken Power Query. 

...

I haven't covered functions - eg simple excel functions like IF and CONCATENATE and they are really basic - exactly like excel but different syntax (TIP: Case is important!). See the article about functions below. . See the article about functions below. 

Microsoft have created a whole new language for functions - the M Language - See the official formulas reference https://support.office.com/en-us/article/Power-Query-formula-categories-125024ec-873c-47b9-bdfd-b437f8716819

Then there is another whole area of coded functions that I haven't even looked into yet. 

Just search for Excel Power Query X Function to find what you want. Oh, and if you find a hyperlink function, let me know in the comments! 

And then there is the whole other layer of Power BI. That is for another page later. 

Related Articles