To extract the information from Confluence, it may be a simple task but if I want to fetch the content including tables and sentences, there are extra steps to do. Before to begin, I will need 3 things:

• My email and my token (to create the token, read Manage API tokens for your Atlassian account). Take note that there is no need to convert your API token into BASE64 encode
• The “ID” of my confluence page (for instance, 123456789)
  

I will use the “get pages” REST API v2 (for more information, read Atlassian Rest API v2).

Once I have all of them, follow those steps.

1. Open Power BI and click on “home -> get data -> web”:

2. Put the URL then click “OK:

URL/wiki/api/v2/pages/youridpage?body-format=storage

• URL for API token with scopes:
  https://api.atlassian.com/ex/jira/yourcloudid
  
• URL for API token without scopes:
  https://yourjira
  

NOTE:

• To not repeat everything in double, for this article, I will use the second URL
• Change yourjira, yourcloudid and youridpage by yours. To get the cloudid, put this in a browser: https://yourjira/_edge/tenant_info

In the authentication window, select “basic” and I put my credentials:

NOTE: this window will not appear anymore once I will be connected to Confluence but if it doesn’t appear for your first connection, go to the “edit credentials” section to edit it if needed

3. It will open the Power Query Editor, before to continue, I will change the name:

4. Click on “advanced editor”:

5. Remove everything below “source” to have only this:

6. Add this below “source”:

bsvalue = Source[body][storage][value]
in
bsvalue

If everything goes well, I should get a html code:

7. Copy the html code then open “notepad” to paste it and save it with the “html” extension with a new name:

8. Back to Power Query Editor, click on “home -> new source -> text/CSV”:

9. Click on the “right” field to select this option then on the “left” field, select the “notepad” file:

If everything goes well, I will get the “navigator” popup:

10. From here, based on what I will select, I will follow those steps:

• a. For table
  After selecting “table 3” for instance, I will click on “advanced editor” then copy everything below the 2nd line (green section):
  
• b. For text
  After selecting “displayed text”, I will click on “advanced editor” then copy everything below the 2nd line (green section):
  

11. Select “source” then “advanced editor”:

12. After [value], add a comma -> [value], and replace the green section by what I copied:

• a. For table
  Additionally, change “source” by “bsvalue” (green lines)
  
  
• b. For text
  Additionally, for the green lines:
  
  • Change “source” by “bsvalue”
  • Change “body” by “p”
  • Remove {0}
  
  
  Click on “list tools -> to table”:
  
  Click “OK”:
  
  Result:
  
  NOTE:
  
  • Alternatively, right click on the header then select “to table”
    
  • I put “p” because in the HTML code, the sentences are coded between <p> and </p> by following the logic of the Html.Table function combining with the correct CSS selector

13. I will delete the queries with the “notepad” file (in my example, “table 3” and/or “displayed text”)

14. Once done, click on “home -> close & apply”

If I want to have both (table and text) and/or more tables , I will need to have 1 source for each because it is not possible to have all in 1 single source. For instance, to get “table 2”, “table 3” and “displayed text”, I will have 3 sources.

Power BI Desktop: edit credentials

To edit my credentials, I will go to “global permissions” in “data source settings” then right click on the first “atlassian.net” and click on “edit permissions”

Click on “edit”

In the “user name”, I put my account and in the “password”, I put my API token

Once done, repeat the same things for the other “atlassian.net”.

Power BI Service: edit credentials

Once published in Power BI Service, in order to configure the automatic refresh, in “data source credentials”, click on this option:

Configure like in the picture according to your token (in the “user name”, I put my account and in the “password”, I put my API token) then click on “sign in”:

For API token with scopes	For API token without scopes