How to Create Connection Between Anaplan and Power BI with API 2.0
Power BI is a popular reporting tool. One of the reasons is that in Power BI you can get data from different sources, and there is an increased interest of our customers and Anaplan builders to be able to import data from Anaplan. In order to make this connection, we developed a solution to set up the integration.
It is possible to have this integration between Anaplan and Power BI using Anaplan Rest APIs. Anaplan released two versions of Rest APIs — 1.3 and 2.0. For version 1.3, the authentication to Anaplan can be done using user and password (basic authentication).
For the Anaplan Rest API version 2.0, the authentication can be done only using a token authenticated, so if version 2.0 will be used, then a token string should be created and should be passed on the actions.
Kindly follow the below process:
In the next instructions, we will see how to create the token from a CA Certificate and authenticate into Anaplan in a single Power BI query. The whole code will be explained in this article.
- Step 1: Let’s start with a Blank query as a source. Right-click over the new query and select Advanced Editor:
- In this first step, we need to create the token. For this, you will need to add your CA Certificate content, the encodedData, and encondedSignedData strings. Please have a look at the below link for more information: Authentication-Strings
Basic authentication can be used for this as well, however, please keep in mind the need to encode it first (password can be easily encrypted via the following link base64encode with the format email: password).
- Click on Done, so the string will be shown that needs to be authenticated :
- Step 2: Come back to the Advanced Editor and send all the previous code into parenthesis to the variable mytoken1. After that the code should be added to run the export action. Replace the workspace, model, and export with your IDs. Use Anaplan Connect or Postman to get them.
- Click on Done and the Export action will be launched. Later, we will come back to this query, as in the next step we will need to download the file.
- Step 3: Now we need the code to download the file. I recommend creating an API with version 1.3. It is only to collect the code because the number of columns and types are different in each file. Add a new Web source -> Advanced
Fill with this information:
Add 3 headers:
- Authorization Basic Your_encoded_password
- Content-Type application/csv; charset=UTF-8 --csv or txt depending of your file
- Accept application/octet-stream
- Click OK, and Power BI will download the file. The Query2 is now added:
- Go to Query2->Advanced Editor, copy the whole code:
- Close the editor and come back to the Query1->Advanced Editor.
Paste at the end of the code the Web.Contents, changing the version from 1/3 to 2/0 and the Authorization with the Token variable.
Paste the TransformColumnTypes without changes.
Finally, the response of the code will be #”Changed Type”. This is the content of your file:
As you can see, I added the function InvokeAfter. This is to give time to Anaplan to finish the export, and then we can continue with the download. I set this up with 20 seconds. Adjust this delay time depending on the size of your file.
- Click OK, and the query will launch the three steps and show the exported data.
- Finally, delete the Query2 and rename Query1 to a proper name.
A final recommendation: if you have a large file, it is better to split the Export over multiple actions because a large file can take a long time to be uploaded into Power BI, and use Chunks will create wrong rows in the concatenation of each one.
Comments
-
I was really hoping someone would create this. Thank you one hundred times. +5 Kudos.
Can't believe it can be done with V2.0 but you made it work. I thought the token would be too much for Power BI.
Nicely handled.
10 -
Hi,
I did connect Power BI to Anaplan, it woks perfectly at the first time but I could not refresh data after modification either from Power BI Desktop or from the service. I am using the 2.0 API (token authentication) and tried also with 1.3 & basic authentication...
Could you please give any advice or help ?
BR,
0 -
Hi Alex,
Thank you for your reply.
Yes that's what I think but how to refresh it or to force it to be re-created knowing that I have the right script to create it at the biginning?
BR,
0 -
Yet another mazing contribution @ABerenguela! Muchas gracias 🙂
0 -
Excellent post!
I've been doing some small demos about this and did not yet have CA Certificates available so I was just using the basic authentication. At first I was struggling a bit with creating the authentication token using basic authentication. Below is a script for how to achieve that in case someone is struggling with the same issue. The solution was to input blank string to Content (initially I just removed the whole line).
-------------------------------------------------------------------------------------------------------
let
//Step 1
mytoken1=(let
user_pw = "Use base64 encoder to encode username:password",
url = "https://auth.anaplan.com/token/authenticate",
header= [#"Authorization" = "Basic " & user_pw],
webdata1 = Web.Contents(url, [Headers=header,Content = Text.ToBinary("")]),
response1 = Json.Document(webdata1),
tokenInfo = "AnaplanAuthToken " & response1[tokenInfo][tokenValue]
in
tokenInfo),
#"auth_key2" = mytoken1,
//Step 2---------------------------------------------------------------------------
0
