As promised here, in today’s post I am sharing an Excel template that analyzes Salesforce Opportunities, and allows you, with a single click of Refresh All in Excel’s Data tab, to get the latest data from Salesforce and auto-populate it into the report. You can download the template here.
Due to a recent authentication change by Saleforce, you may fail to refresh workbooks with Salesforce connections. But furtunately, you can apply a simple workaround to fix this issue.
Salesforce Login Failure and Workarounds
If you get login error when you try connecting to Salesforce, as shown below, you can find the workaround in this section (Or go here, if you prefer reading it in Power Query TechNet forum).
The Excel template uses Power Query’s Salesforce connector that uses TLS 1.0 to autheticate with Salesforce, but Salesforce recently disabled TLS 1.0 (read more here), and as a result, you get the error above when you try connecting.
So what could you do?
First, lets determine your Power Query engine version:
In Excel 2016, click Data –> New Query –> Query Options
In Excel 2010, or 2013, click Power Query –> Options
In Query Options, select Diagnostics, the check the version as highlighted below.
Solution #1 for Power Query v2.40 or above.
If you have Power Query version 2.40 or above, you can follow these steps:
- Open File Explorer.
- Right click on This PC and select Properties.
- Select Advanced system settings.
- Select Advanced tab.
- Click Environment Variables.
- Create a new variable by clicking the New button
- Set the new variable’s name as PQ_EnableTls12ForOAuth, and its value to be true.
- Click OK, and restart Excel.
Solution #2 (Mainly relevant for Power Query Add-In users)
If you don’t have Power Query version 2.40, follow these steps, or contact your Salesforce administrator:
Note: Power Query version 2.40 will be out soon (It is already available in the early release of Excel 2016), so the following solution is only a temporary one.
- After you login to Salesforce, click the Settings icon (HIghlighted as step 1 in the screenshot below).
- In the search box (step 2 below) search for update.
- Select Critical Updates (step 3).
- Click Deactivate on the update which starts with Require TLS 1.1…, (step 4) and confirm the deactivation in the next window.
- Make sure you are aware of the end period for this workaround. Salesforce will automatically activate the critical update that disables TLS 1.0 in the highlighted date (step 5).
Now that we are able to connect to Salesforce with Power Query we are ready to proceed with the template.
Requirements to use the template
To use the template you should have one of the following Microsoft products:
- Excel 2016
- Excel 2013 with Microsoft Power Query Add-In (Download it here)
- Microsoft Power BI Desktop (I will publish a PBIX file in the near future. For now, you can import the Excel file into Power BI Desktop, and load its queries and Data Model, and start building your own visuals)
How to use the Salesforce Opportunities Template
Download the template here, and open it with Excel 2016, or Excel 2013 with Power Query Add-In. Click Let’s get started.
Click Connect.
In Access Salesforce window, click Sign in.
Provide your Salesforce credentials, and click Log In.
Click Allow in the next window to allow Power BI App to communicate with Salesforce on your behalf.
Back in Access Salesforce, click Connect. If you have an authentication error, make sure you correctly followed the workarounds in the first section above.
You may need to wait a minute or more, depending on the volumn of data on your Salesforce. At the end you will get the following report.
In the next episode in the Salesforce series, we will go over the Power Query steps that allowed us to build this report.
Hope you enjoy the template.
Happy holidays and wishing you the best for 2017.
This is really cool!! one quick question, do you know how to get Amount Converted field, when using Multicurrency implementation?.
Thansk for sharing!!
Thank you Iñaki,
I’ve asked Salesforce to switch my account to Multi Currency, and will try to find the solution. You probably need to change the query according to this article: https://developer.salesforce.com/docs/atlas.en-us.object_reference.meta/object_reference/sforce_api_objects_opportunity.htm
If you need further help, contact me at gilra@datachant.com
Hi Gil, many thanks indeed for answering!
As i have seen this is calculated on real time using a function convertCurrency(amount), i will play around to see if i am able to get somethingh! Thanks very much for your time!
Well finally i get a solution, for the current month i get the rate from currency_type object and calculate the amount, and for the other amounts i get the value from the field Amount_FX_Master. I calculate the final value using a measure.
Thanks!!