Hello,
I have included a table in Power BI that prevent Automatic refresh to permorm with the following error :
"This report contains dynamic datasources, which cannot be refreshed in Power BI Report Server."
The code of the table that is generating the error is this one. Any Help would be much appraciated.
let
Source = Json.Document(Web.Contents("https://myserver1.ge-admin.ad.my_comp.fr/reports/api/v2.0/PowerBIReports")),
#"Converti en table" = Table.FromRecords({Source}),
#"value développé" = Table.ExpandListColumn(#"Converti en table", "value"),
#"value développé1" = Table.ExpandRecordColumn(#"value développé", "value", {"Id", "Name", "Description", "Path", "Type", "Hidden", "Size", "ModifiedBy", "ModifiedDate", "CreatedBy", "CreatedDate", "ParentFolderId", "IsFavorite", "ContentType", "Content", "HasDataSources", "Roles"}, {"value.Id", "value.Name", "value.Description", "value.Path", "value.Type", "value.Hidden", "value.Size", "value.ModifiedBy", "value.ModifiedDate", "value.CreatedBy", "value.CreatedDate", "value.ParentFolderId", "value.IsFavorite", "value.ContentType", "value.Content", "value.HasDataSources", "value.Roles"}),
#"Type modifié" = Table.TransformColumnTypes(#"value développé1",{{"@odata.context", type text}, {"value.Id", type text}, {"value.Name", type text}, {"value.Description", type text}, {"value.Path", type text}, {"value.Type", type text}, {"value.Hidden", type logical}, {"value.Size", Int64.Type}, {"value.ModifiedBy", type text}, {"value.ModifiedDate", type datetimezone}, {"value.CreatedBy", type text}, {"value.CreatedDate", type datetimezone}, {"value.ParentFolderId", type text}, {"value.IsFavorite", type logical}, {"value.ContentType", type any}, {"value.Content", type any}, {"value.HasDataSources", type logical}, {"value.Roles", type any}}),
#"Colonnes renommées" = Table.RenameColumns(#"Type modifié",{{"value.Name", "NomRapport"}, {"value.Id", "IdRapport"}, {"value.Path", "CheminRapport"}}),
#"Littéral inséré" = Table.AddColumn(#"Colonnes renommées", "Littéral", each "opk", type text),
#"Colonnes renommées1" = Table.RenameColumns(#"Littéral inséré",{{"Littéral", "CustomURL"}}),
#"Personnalisée ajoutée" = Table.AddColumn(#"Colonnes renommées1", "URL", each "https://pbirsot.rec.my_comp.fr/reports/api/v2.0/PowerBIReports(" & Text.From([IdRapport]) & ")/DataSources"),
#"Type modifié1" = Table.TransformColumnTypes(#"Personnalisée ajoutée",{{"URL", type text}}),
#"Personnalisée ajoutée1" = Table.AddColumn(#"Type modifié1", "ContentDataSource", each Web.Contents([URL])),
#"JSON analysé" = Table.TransformColumns(#"Personnalisée ajoutée1",{{"ContentDataSource", Json.Document}}),
#"ContentDataSource développé" = Table.ExpandRecordColumn(#"JSON analysé", "ContentDataSource", {"@odata.context", "value"}, {"ContentDataSource.@odata.context", "ContentDataSource.value"}),
#"ContentDataSource.value développé" = Table.ExpandListColumn(#"ContentDataSource développé", "ContentDataSource.value"),
#"ContentDataSource.value développé1" = Table.ExpandRecordColumn(#"ContentDataSource.value développé", "ContentDataSource.value", {"Id", "Name", "Description", "Path", "Type", "Hidden", "Size", "ModifiedBy", "ModifiedDate", "CreatedBy", "CreatedDate", "ParentFolderId", "IsFavorite", "ContentType", "Content", "IsEnabled", "ConnectionString", "DataSourceType", "IsOriginalConnectionStringExpressionBased", "IsConnectionStringOverridden", "CredentialRetrieval", "IsReference", "DataSourceSubType", "Roles", "CredentialsByUser", "CredentialsInServer", "DataModelDataSource"}, {"ContentDataSource.value.Id", "ContentDataSource.value.Name", "ContentDataSource.value.Description", "ContentDataSource.value.Path", "ContentDataSource.value.Type", "ContentDataSource.value.Hidden", "ContentDataSource.value.Size", "ContentDataSource.value.ModifiedBy", "ContentDataSource.value.ModifiedDate", "ContentDataSource.value.CreatedBy", "ContentDataSource.value.CreatedDate", "ContentDataSource.value.ParentFolderId", "ContentDataSource.value.IsFavorite", "ContentDataSource.value.ContentType", "ContentDataSource.value.Content", "ContentDataSource.value.IsEnabled", "ContentDataSource.value.ConnectionString", "ContentDataSource.value.DataSourceType", "ContentDataSource.value.IsOriginalConnectionStringExpressionBased", "ContentDataSource.value.IsConnectionStringOverridden", "ContentDataSource.value.CredentialRetrieval", "ContentDataSource.value.IsReference", "ContentDataSource.value.DataSourceSubType", "ContentDataSource.value.Roles", "ContentDataSource.value.CredentialsByUser", "ContentDataSource.value.CredentialsInServer", "ContentDataSource.value.DataModelDataSource"}),
#"ContentDataSource.value.DataModelDataSource développé" = Table.ExpandRecordColumn(#"ContentDataSource.value développé1", "ContentDataSource.value.DataModelDataSource", {"Type", "Kind", "AuthType", "SupportedAuthTypes", "Username", "Secret", "ModelConnectionName"}, {"ContentDataSource.value.DataModelDataSource.Type", "ContentDataSource.value.DataModelDataSource.Kind", "ContentDataSource.value.DataModelDataSource.AuthType", "ContentDataSource.value.DataModelDataSource.SupportedAuthTypes", "ContentDataSource.value.DataModelDataSource.Username", "ContentDataSource.value.DataModelDataSource.Secret", "ContentDataSource.value.DataModelDataSource.ModelConnectionName"}),
#"Personnalisée ajoutée2" = Table.AddColumn(#"ContentDataSource.value.DataModelDataSource développé", "RapportID", each Text.Upper([IdRapport])),
#"Type modifié2" = Table.TransformColumnTypes(#"Personnalisée ajoutée2",{{"RapportID", type text}}),
#"Personnalisée ajoutée3" = Table.AddColumn(#"Type modifié2", "Environnement", each "REC"),
#"Autres colonnes supprimées" = Table.SelectColumns(#"Personnalisée ajoutée3",{"IdRapport", "NomRapport", "CheminRapport", "value.Type", "value.Size", "value.ModifiedBy", "value.ModifiedDate", "value.CreatedBy", "value.CreatedDate", "ContentDataSource.value.ModifiedBy", "ContentDataSource.value.ModifiedDate", "ContentDataSource.value.CreatedBy", "ContentDataSource.value.CreatedDate", "ContentDataSource.value.ConnectionString", "ContentDataSource.value.DataModelDataSource.Kind", "ContentDataSource.value.DataModelDataSource.AuthType", "ContentDataSource.value.DataModelDataSource.Username", "RapportID", "Environnement"}),
#"Colonnes renommées2" = Table.RenameColumns(#"Autres colonnes supprimées",{{"value.ModifiedBy", "Report_ModifiedBy"}, {"ContentDataSource.value.DataModelDataSource.Username", "DataSourceAuthUsername"}, {"ContentDataSource.value.DataModelDataSource.Kind", "DataSourceKind"}, {"value.ModifiedDate", "Report_ModifiedDate"}, {"value.CreatedBy", "Report_CreatedBy"}, {"value.CreatedDate", "Report_CreatedDate"}, {"ContentDataSource.value.ConnectionString", "ConnectionString"}, {"ContentDataSource.value.DataModelDataSource.AuthType", "DataSourceAuthType"}}),
#"Colonnes permutées" = Table.ReorderColumns(#"Colonnes renommées2",{"Environnement", "RapportID", "IdRapport", "NomRapport", "CheminRapport", "value.Type", "value.Size", "Report_ModifiedBy", "Report_ModifiedDate", "Report_CreatedBy", "Report_CreatedDate", "ContentDataSource.value.ModifiedBy", "ContentDataSource.value.ModifiedDate", "ContentDataSource.value.CreatedBy", "ContentDataSource.value.CreatedDate", "ConnectionString", "DataSourceKind", "DataSourceAuthType", "DataSourceAuthUsername"}),
#"Lignes triées" = Table.Sort(#"Colonnes permutées",{{"RapportID", Order.Ascending}})
in
#"Lignes triées"
