Analyze Salesforce Opportunities in Excel + Workaround for the login failures in #PowerQuery Salesforce connector

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

Salesforce login error using Power Query

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.

How to find Power Query version?

Solution #1 for Power Query v2.40 or above.

If you have Power Query version 2.40 or above, you can follow these steps:

  1. Open File Explorer.
  2. Right click on This PC and select Properties.
  3. Select Advanced system settings.
  4. Select Advanced tab.
  5. Click Environment Variables.
  6. Create a new variable by clicking the New button
  7. Set the new variable’s name as PQ_EnableTls12ForOAuth, and its value to be true.Fix Salesforce login issue in Excel Power Query
  8. 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.

  1. After you login to Salesforce, click the Settings icon (HIghlighted as step 1 in the screenshot below).
  2. In the search box (step 2 below) search for update.
  3. Select Critical Updates (step 3).
  4. Click Deactivate on the update which starts with Require TLS 1.1…, (step 4) and confirm the deactivation in the next window.
  5. 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).

Enable TLS 1.0 in Salesforce till Power Query v2.40 is released

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:

  1. Excel 2016
  2. Excel 2013 with Microsoft Power Query Add-In (Download it here)
  3. 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.

Salesforce Opportunites Template in Excel

Click Connect.

Salesforce Opportunites Template in Excel

In Access Salesforce window, click Sign in.

Salesforce Opportunites Template in Excel

Provide your Salesforce credentials, and click Log In.

Salesforce Opportunites Template in Excel

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.

Salesforce Opportunites Template in Excel

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.

Salesforce Opportunites Template in Excel

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.

4 comments

  1. Iñaki beltran Reply

    This is really cool!! one quick question, do you know how to get Amount Converted field, when using Multicurrency implementation?.

    Thansk for sharing!!

  2. Iñaki beltran Reply

    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!

  3. Iñaki beltran Reply

    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!!

Leave a Reply