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.
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 linkbase64encodewith the format email: password).
Fill with this information:
Add 3 headers:
Paste at the end of the code the Web.Contents, changing the version from1/3to2/0and the Authorization with the Token variable.
Paste the TransformColumnTypes without changes.
Finally, the response of the code will be#“改变类型”。This is the content of your file:
As you can see, I added the functionInvokeAfter.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.
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.
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.
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,
@anorifrom your description of the issue, if it worked for the first time, it means that the token is not correctly refreshed or re-created.
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,
Yet another mazing contribution@ABerenguela! Muchas gracias
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
---------------------------------------------------------------------------