Read all data from dalux Api

[Atoma]

Hello everyone,

I'm trying to link PowerBi to the opening API of an application we're using. Powerbi can only read data from one page. I would like it to read all the data. If you take the task. How can I read all the pages containing data? lien vers le .pbix 

[TheSimpleConstr]

@Atoma 

Did you fix your problem and are willing to share your results? 
I have the exact same problem - i cant read al task data because it only returns the first 100 rows.
Can't seem te use paging or Table.GenerateByPage to find the right way to read all the data.

Thanks in advance

[Atoma]

Hello @TheSimpleConstr , I still have the same problem. I haven't dug any deeper into the subject ... I look forward to hearing from you

[dmbd1904]

If you get stuck we have a ready made solution in Fabric https://acumine.com/dalux-integration-with-microsoft-fabric-and-power-bi/

[dmbd1904]

I think what you require is pagination and you could probably find some relevant information on google, or this forum. We work with the Dalux API a lot for our construciton clients but find it is more effective to work with it using the Data Engineering tools within Microsoft Fabric

[Anonymous]

Hi @Atoma ,

If you are trying to read all the pages from an API, you can use the REST API for Power BI. The API returns a list of pages within the specified report from My workspace. You can use this API(Reports - Get Pages)to get all the pages containing data. The required scope for this API is or Report.ReadWrite.AllReport.Read.All

Below is the official link will help you:
Reports - Get Pages - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Atoma]

thank you for your reply. This function lets you know how many pages the API contains. I just want powerbi to read all the pages containing data. As the number of pages can change, I would like this to be automatic. Without manually filling in the number of pages each time it is updated.

[Atoma]

the code since is this one : 

let
Source = Json.Document(Web.Contents("https://field.dalux.com/service/api" & "/4.0/projects/6663914611/tasks", [Headers=[#"X-API-KEY"="290d553c-c523-43f1-9eb0-4f241ed51802"]])),
#"Converti en table" = Table.FromRecords({Source}),
#"items développé" = Table.ExpandListColumn(#"Converti en table", "items"),
#"items développé1" = Table.ExpandRecordColumn(#"items développé", "items", {"taskId", "subject", "usage", "type", "number", "created", "createdBy", "workflow", "location", "userDefinedFields"}, {"taskId", "subject", "usage", "type", "number", "created", "createdBy", "workflow", "location", "userDefinedFields"}),
#"type développé" = Table.ExpandRecordColumn(#"items développé1", "type", {"name"}, {"name"}),
#"createdBy développé" = Table.ExpandRecordColumn(#"type développé", "createdBy", {"userId"}, {"userId"}),
#"workflow développé" = Table.ExpandRecordColumn(#"createdBy développé", "workflow", {"name"}, {"name.1"}),
#"location développé" = Table.ExpandRecordColumn(#"workflow développé", "location", {"level", "room", "building", "drawing", "locationImages"}, {"level", "room", "building", "drawing", "locationImages"}),
#"level développé" = Table.ExpandRecordColumn(#"location développé", "level", {"name"}, {"name.2"}),
#"room développé" = Table.ExpandRecordColumn(#"level développé", "room", {"name"}, {"name.3"}),
#"building développé" = Table.ExpandRecordColumn(#"room développé", "building", {"name"}, {"name.4"}),
#"drawing développé" = Table.ExpandRecordColumn(#"building développé", "drawing", {"name"}, {"name.5"}),
#"locationImages développé" = Table.ExpandListColumn(#"drawing développé", "locationImages"),
#"locationImages développé1" = Table.ExpandRecordColumn(#"locationImages développé", "locationImages", {"name", "fileDownload"}, {"name.6", "fileDownload"}),
#"userDefinedFields développé" = Table.ExpandRecordColumn(#"locationImages développé1", "userDefinedFields", {"items"}, {"items"}),
#"items développé2" = Table.ExpandListColumn(#"userDefinedFields développé", "items"),
#"items développé3" = Table.ExpandRecordColumn(#"items développé2", "items", {"key", "name", "values"}, {"key", "name.7", "values"}),
#"metadata développé" = Table.ExpandRecordColumn(#"items développé3", "metadata", {"totalItems"}, {"totalItems"})
in
#"metadata développé"

[DCF_Sylvain]

Hello, I'm working on Dalux and connected it to Power BI with API. I'm not sure to understand what you meant by the fact that you can't have all the pages. If it's still the case, you can send me a message that we have a look on it.