How to Know Which Report Storage Mode is Import or Direct Query In a Workspace

Hi ManasRout,

 

Using Power Query, you can call the Power BI REST APIs to retrieve the list of Reports and Datasets, merge them, and create a report that shows which reports are connected to Import, DirectQuery, or Composite semantic models.

 

1. Retrieve Datasets - 

In Power BI Desktop → Get Data → Blank Query, open Advanced Editor and paste the following code. Replace WORKSPACE_ID with your workspace ID and BEARER_TOKEN you can find in the API call page (highlighted in grey). When prompted for credentials, choose Anonymous.

This query returns DatasetID, DatasetName, and StorageMode. The ContentProviderType property can be used to identify whether the semantic model is Import, DirectQuery, or Composite.

let
    WorkspaceId = "WORKSPACE_ID",
    Token = "BEARER_TOKEN",

    Source =
        Json.Document(
            Web.Contents(
                "https://api.powerbi.com",
                [
                    RelativePath = "v1.0/myorg/groups/" & WorkspaceId & "/datasets",
                    Headers = [
                        Authorization = "Bearer " & Token,
                        Accept = "application/json"
                    ]
                ]
            )
        ),

    Value = Source[value],
    ConvertToTable = Table.FromList(Value, Splitter.SplitByNothing()),
    ExpandData = Table.ExpandRecordColumn(
        ConvertToTable,
        "Column1",
        {"id","name","ContentProviderType"},
        {"DatasetID","DatasetName","StorageMode"}
    )
in
    ExpandData

2. Retrieve Reports - 

Using Get Reports In Group – REST API, you can get all the reports in a Workspace in a JSON format by adding the WORKSPACE_ID (groupId) in the API call.

Reference - Reports - Get Reports In Group - REST API (Power BI Power BI REST APIs) | Microsoft Learn

Create another Blank Query and paste the following code to retrieve all reports from the same workspace.

This query returns ReportID, ReportName, and DatasetID.

let
    WorkspaceId = "WORKSPACE_ID",
    Token = "BEARER_TOKEN",

    Source =
        Json.Document(
            Web.Contents(
                "https://api.powerbi.com",
                [
                    RelativePath = "v1.0/myorg/groups/" & WorkspaceId & "/reports",
                    Headers = [
                        Authorization = "Bearer " & Token,
                        Accept = "application/json"
                    ]
                ]
            )
        ),

    Value = Source[value],
    ConvertToTable = Table.FromList(Value, Splitter.SplitByNothing()),
    ExpandData = Table.ExpandRecordColumn(
        ConvertToTable,
        "Column1",
        {"id","name","datasetId"},
        {"ReportID","ReportName","DatasetID"}
    )
in
    ExpandData

3. Merge both the queries by Reports[DatasetID] = Datasets[DatasetID]

4. Expand DatasetName & StorageMode

5. Add Custom Column using below code. Have only required columns and rename accordingly in the table.

if [StorageMode] = "PbixInImportMode" then "Import"
else if [StorageMode] = "PbixInDirectQueryMode" then "DirectQuery"
else if [StorageMode] = "PbixInCompositeMode" then "Composite"
else [StorageMode]

Sample Result - 

💡 Helpful? Give a Kudos 👍 — keep the community growing
✅ Solved your issue? Mark as Solution ✔️ — help others find it faster

Best regards,
Rupasree Achari | BI & Fabric Analytics Engineer