# Sailpoint x PowerBI API

**URL:** <https://developer.sailpoint.com/discuss/t/sailpoint-x-powerbi-api/64073>\
**Category:** SHF Discussion and Questions\
**Tags:** identity-security-cloud, apis\
**Created:** [June 7, 2024, 12:42pm UTC](https://developer.sailpoint.com/discuss/t/sailpoint-x-powerbi-api/64073 "2024-06-07T12:42:49Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![kaiolima](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/kaiolima/32/7226_2.png) [@kaiolima](https://developer.sailpoint.com/discuss/u/kaiolima)\
**Post date:** [June 7, 2024, 12:42pm UTC](https://developer.sailpoint.com/discuss/t/sailpoint-x-powerbi-api/64073/1 "2024-06-07T12:42:49Z")

</div>

Hello, I would like to know how I could transfer some attribute information to powerbi? I need to get two fields from the SailPoint API and pull them through Power BI.

I saw that this process exists, but it is not bringing it.

 ![1](https://global.discourse-cdn.com/sailpoint/original/2X/f/fb7a4b9bafaa196105b0fc10ca1e4362f8eea9e5.png)

I need to bring this information:

{  
“indexes”: [  
“identities”  
],  
“query”: {  
“query”: “attributes.cloudLifecycleState:holidays”

},  
“includeNested”: true ,  
“queryResultFilter”: {  
“includes”: [  
“\*”

]  
}

}

---

<div class="post-metadata">

**Author:** ![baoussounda](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/baoussounda/32/13095_2.png) [@baoussounda](https://developer.sailpoint.com/discuss/u/baoussounda)\
**Post date:** [June 7, 2024, 1:46pm UTC](https://developer.sailpoint.com/discuss/t/sailpoint-x-powerbi-api/64073/2 "2024-06-07T13:46:51Z")

</div>

Hi @kaiolima,

I suggest to use Adanced Power Query script :

 ![image](https://global.discourse-cdn.com/sailpoint/original/2X/6/6ae1dc3d3a6612cbbdb54f8d249ab9c697752c9a.png)

Here the content of script :

```auto
let
    url = "https://dummyjson.com/posts/add",
    headers = [#"Content-Type"="application/json"],
    body = "{ ""title"": ""Test title"", ""userId"": 5 }",
   response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary((body))
        ]))
in
 response

```

And template isc/idn :

```auto
let
    url = "https://tenant.api.identitynow.com/v3/search",
    headers = [#"Content-Type"="application/json",Authorization="Bearer yourAccessToken"],
    body = "{ ""indexes"": [""identities""], ""query"": { ""query"": ""attributes.cloudLifecycleState:holidays"" }, ""includeNested"": false, ""queryResultFilter"": { ""includes"": [""*""] } }",
   response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary((body))
        ]))
in
 response

```

Replace yourAccessToken with your access token.

Be review your search query if it’s what you really want.

---

<div class="post-metadata">

**Author:** ![vguleria](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/vguleria/32/7884_2.png) [@vguleria](https://developer.sailpoint.com/discuss/u/vguleria)\
**Post date:** [June 7, 2024, 2:09pm UTC](https://developer.sailpoint.com/discuss/t/sailpoint-x-powerbi-api/64073/3 "2024-06-07T14:09:37Z")

</div>

Hi @kaiolima

Yes, agree with @baoussounda , you will need to use the powerQuery code to make it possible.

You need to do as below

1. Retrieve the access token by giving your using client credentials using the PAT.
2. Then you need to make another POST API call to call the search API. Personally, i use the report extraction APIs because with search API, we need to add the pagination logic (which is possible) but powerBI does give the issues when the identities exceeds more than 10K. So in that case, it is better to use report extraction API.

In addition to that there was also a very nice demo presented during 2024 developer days. You can check out the amazing session

> [@BYOB - bring your own BI](https://developer.sailpoint.com/discuss/t/byob-bring-your-own-bi/37615):
>
> [[BYOB - bring your own BI] ](https://www.youtube.com/watch?v=8X8Mjbgvocg)Description Interested in using your Identity Security Cloud data with Microsoft’s PowerBI? Yannick will show you how to create a Custom Connector for PowerBI leveraging Identity Security Cloud’s APsI to create appealing dashboards for your business.

I hope this helps. If you need more information, please let me know, happy to help.

Thank You.  
Regards  
Vikas.

---

<div class="post-metadata">

**Author:** ![kaiolima](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/kaiolima/32/7226_2.png) [@kaiolima](https://developer.sailpoint.com/discuss/u/kaiolima)\
**Post date:** [June 7, 2024, 3:25pm UTC](https://developer.sailpoint.com/discuss/t/sailpoint-x-powerbi-api/64073/4 "2024-06-07T15:25:12Z")

</div>

Thank you, it worked here.

It just appears in a strange way, you have to click on each “record”

 ![1](https://global.discourse-cdn.com/sailpoint/original/2X/0/0e362eb22330c352b4014be621d9d3fa140e471f.png)

---

<div class="post-metadata">

**Author:** ![baoussounda](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/baoussounda/32/13095_2.png) [@baoussounda](https://developer.sailpoint.com/discuss/u/baoussounda)\
**Post date:** [June 7, 2024, 3:31pm UTC](https://developer.sailpoint.com/discuss/t/sailpoint-x-powerbi-api/64073/5 "2024-06-07T15:31:15Z")

</div>

I think because each entry is object.  
Try this :

```auto
 url = "https://tenant.api.identitynow.com/v3/search",
    headers = [#"Content-Type"="application/json",Authorization="Bearer yourAccessToken"],
    body = "{ ""indexes"": [""identities""], ""query"": { ""query"": ""attributes.cloudLifecycleState:holidays"" }, ""includeNested"": false, ""queryResultFilter"": { ""includes"": [""*""] } }",
   response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary((body))
        ]))

  // Convert the list of records into a table
    itemsTable = Table.FromList(response, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    // Expand the records into columns
    expandedItems = Table.ExpandRecordColumn(itemsTable, "Column1", Record.FieldNames(response{0}))
in
    expandedItem

```

---

<div class="post-metadata">

**Author:** ![kaiolima](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/kaiolima/32/7226_2.png) [@kaiolima](https://developer.sailpoint.com/discuss/u/kaiolima)\
**Post date:** [June 7, 2024, 3:47pm UTC](https://developer.sailpoint.com/discuss/t/sailpoint-x-powerbi-api/64073/6 "2024-06-07T15:47:22Z")

</div>

There is an error in the itemTable

 ![11](https://global.discourse-cdn.com/sailpoint/original/2X/0/086fdafd3032ae3f4d740fb142f3af13308d6075.jpeg)

---

<div class="post-metadata">

**Author:** ![baoussounda](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/baoussounda/32/13095_2.png) [@baoussounda](https://developer.sailpoint.com/discuss/u/baoussounda)\
**Post date:** [June 7, 2024, 3:50pm UTC](https://developer.sailpoint.com/discuss/t/sailpoint-x-powerbi-api/64073/7 "2024-06-07T15:50:58Z")

</div>

@kaiolima i update it :

```auto
url = "https://tenant.api.identitynow.com/v3/search",
    headers = [#"Content-Type"="application/json",Authorization="Bearer yourAccessToken"],
    body = "{ ""indexes"": [""identities""], ""query"": { ""query"": ""attributes.cloudLifecycleState:holidays"" }, ""includeNested"": false, ""queryResultFilter"": { ""includes"": [""*""] } }",
   response = Json.Document(Web.Contents(url,
        [
            Headers = headers,
            Content = Text.ToBinary((body))
        ])),

  // Convert the list of records into a table
    itemsTable = Table.FromList(response, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    
    // Expand the records into columns
    expandedItems = Table.ExpandRecordColumn(itemsTable, "Column1", Record.FieldNames(response{0}))
in
    expandedItems

```

---

<div class="post-metadata">

**Author:** ![kaiolima](https://sea1.discourse-cdn.com/sailpoint/discuss/user_avatar/developer.sailpoint.com/kaiolima/32/7226_2.png) [@kaiolima](https://developer.sailpoint.com/discuss/u/kaiolima)\
**Post date:** [June 7, 2024, 5:20pm UTC](https://developer.sailpoint.com/discuss/t/sailpoint-x-powerbi-api/64073/8 "2024-06-07T17:20:54Z")

</div>

It works now, thank you!

---

<div class="post-metadata">

**Author:** ![system](https://global.discourse-cdn.com/sailpoint/original/2X/f/f2136700ed5e3703e0b85e02f6be799dacca7735.png) [@system](https://developer.sailpoint.com/discuss/u/system)\
**Post date:** [August 6, 2024, 5:21pm UTC](https://developer.sailpoint.com/discuss/t/sailpoint-x-powerbi-api/64073/9 "2024-08-06T17:21:29Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.