Get-PowerBIDatasource -> connectiondetails

[vdbosschekoen]

With the Get-PowerBIDatasource option i'm able to get the connectiondetails when the sourceType is Sql, Odata, Sharepoint, Web. But is there a way to get the connectiondetails when the sourceType is of the type File? Because we want to have an overview of all the connectiondetails in all our datasets.

[michel-kamp-work]

+1 indeed the connection details for type file is showing empty SQL connection details. See example below. The SQL type is okay but the file type not.

Connect-PowerBIServiceAccount
$x = Get-PowerBIDataset -Scope Organization -Name test | get-PowerBIDatasource -Scope Organization
$x

Name              :
ConnectionString  :
DatasourceType    : Sql
ConnectionDetails : Microsoft.PowerBI.Common.Api.Shared.DatasourceConnectionDetails
GatewayId         : cdccce6f-affe-45e6-acc6-2b5e07b9eb89
DatasourceId      : 6156dd22-d04d-45ac-9fb0-01cbb8e12241

Name              :
ConnectionString  :
DatasourceType    : File
ConnectionDetails : Microsoft.PowerBI.Common.Api.Shared.DatasourceConnectionDetails
GatewayId         : cdccce6f-affe-45e6-acc6-2b5e07b9eb89
DatasourceId      : 706a6c05-0d0c-4598-8000-1fe67d036dc9

# show sql
$x[0].ConnectionDetails
Server Database Url
------ -------- ---
sql01  master  

# show file
$x[1].ConnectionDetails
Server Database Url
------ -------- ---

When i do the same using the Graph API i get the correct file info:

Connect-PowerBIServiceAccount
$datasetId = "e4f4ad5a-3bb4-4947-bcc2-f449f9c2d338"
$bearerToken = (Get-PowerBIAccessToken -AsString)
$headers = @{"Authorization"="$bearerToken";"Content-Type"="application/json"};
$uri =  "https://api.powerbi.com/v1.0/myorg/datasets/$datasetId/datasources"
$Response = Invoke-WebRequest -Uri $uri -Method "Get" -Headers $headers -UseBasicParsing
$Response.Content

{
  "@odata.context":"http://wabi-west-us3-a-primary-redirect.analysis.windows.net/v1.0/myorg/$metadata#datasources","value":[
    {
      "datasourceType":"Sql","connectionDetails":{
        "server":"sql01","database":"master"
      },"datasourceId":"0172137c-ec30-45e5-912e-91bce56742f9","gatewayId":"cdccce6f-affe-45e6-acc6-2b5e07b9eb89"
    },{
      "datasourceType":"Sql","connectionDetails":{
        "server":"sql01","database":"msdb"
      },"datasourceId":"6156dd22-d04d-45ac-9fb0-01cbb8e12241","gatewayId":"cdccce6f-affe-45e6-acc6-2b5e07b9eb89"
    },{
      "datasourceType":"File","connectionDetails":{
        "path":"c:\\temp\\testdata.xlsx"
      },"datasourceId":"706a6c05-0d0c-4598-8000-1fe67d036dc9","gatewayId":"cdccce6f-affe-45e6-acc6-2b5e07b9eb89"
    }
  ]
}

Using

get-module MicrosoftPowerBIMgmt.Data
ModuleType Version    PreRelease Name                                ExportedCommands
---------- -------    ---------- ----                                ----------------
Binary     1.2.1111              MicrosoftPowerBIMgmt.Data           {Add-PowerBIDataset, Add-PowerBIRow, Export-PowerBIDataflow, G…

$PSVersionTable
Name                           Value
----                           -----
PSVersion                      7.3.6
PSEdition                      Core
GitCommitId                    7.3.6
OS                             Microsoft Windows 10.0.20348
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

[michel-kamp-work]

looks like it misses all the other datasource types handeling here: src/Common/Common.Api/Shared/DatasourceConnectionDetails.cs

[SAgnihotri200895]

Is there any update on this or any workaround on it other then rest apis ?

[michel-kamp-work]

Not that i know . I implemented a workaround as below . maybe its helps ?

$workspaces = Get-PowerBIWorkspace -Scope Organization -All -Include Datasets
    foreach ($workspace in $workspaces) {
        Write-verbose "Processing workspace: $($workspace.name)"
        $datasets = $workspace.Datasets
        Write-verbose "datasets count:  $($datasets.count)"
        foreach ($dataset in $datasets)
        {
            # get the datasources
            Write-verbose "Processing dataset :  $($dataset.Name)"

           #BUG# $datasources = $dataset | get-PowerBIDatasource -Scope Organization | Select-Object *,@{Name='WorkspaceId'; Expression={$workspace.Id.Guid}} ,@{Name='DatasetId'; Expression={$dataset.Id.Guid}}  

            # !!! due to a bug (the datasource of type file are not correctly returned) in the get-PowerBIDatasource we need to do a rest call
            Write-verbose "Get dataset datasources using restcall" 
            $datasourcesJSON = Invoke-PowerBIRestMethod -Url "admin/datasets/$($dataset.id.Guid)/datasources" -Method Get 
            $datasources = ($datasourcesJSON | ConvertFrom-Json).value | Select-Object *,@{Name='WorkspaceId'; Expression={$workspace.Id.Guid}} ,@{Name='DatasetId'; Expression={$dataset.Id.Guid}}  

            Write-verbose "datasources count:  $($datasource.count)"
            $PowerBIDataSources += $datasources 
        }
    }

[SAgnihotri200895]

Not that i know . I implemented a workaround as below . maybe its helps ?

$workspaces = Get-PowerBIWorkspace -Scope Organization -All -Include Datasets
   foreach ($workspace in $workspaces) {
       Write-verbose "Processing workspace: $($workspace.name)"
       $datasets = $workspace.Datasets
       Write-verbose "datasets count:  $($datasets.count)"
       foreach ($dataset in $datasets)
       {
           # get the datasources
           Write-verbose "Processing dataset :  $($dataset.Name)"

          #BUG# $datasources = $dataset | get-PowerBIDatasource -Scope Organization | Select-Object *,@{Name='WorkspaceId'; Expression={$workspace.Id.Guid}} ,@{Name='DatasetId'; Expression={$dataset.Id.Guid}}  

           # !!! due to a bug (the datasource of type file are not correctly returned) in the get-PowerBIDatasource we need to do a rest call
           Write-verbose "Get dataset datasources using restcall" 
           $datasourcesJSON = Invoke-PowerBIRestMethod -Url "admin/datasets/$($dataset.id.Guid)/datasources" -Method Get 
           $datasources = ($datasourcesJSON | ConvertFrom-Json).value | Select-Object *,@{Name='WorkspaceId'; Expression={$workspace.Id.Guid}} ,@{Name='DatasetId'; Expression={$dataset.Id.Guid}}  

           Write-verbose "datasources count:  $($datasource.count)"
           $PowerBIDataSources += $datasources 
       }
   }

Thanks for prompt response, with this rest api endpoint specifically , i continuously get 404 error suggesting dataset doesn't exists, which is not correct , i have validated . I also have power platform admin role assigned to my account but still getting this issue for this endpoint..

Is it because im using power bi free account license that is why isnt allowing ?