User Profile
DaxNovice
Frequent Visitor
Joined 6 years ago
User Widgets
Contributions
Re: Dynamically query all SharePoint sites with same list name, but can't refresh online
I have a similar issue, A list of sharepoint sites stored in a list. I just wat to know how many files have been submitted to each site each month Works fine on pbix, but too dynamic for dataflow or service refresh. This should be possible, its within Microsoft's infrastructure and surely not that unusual let Source = (SP as text ) => let Source = SharePoint.Files(SP), #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "/Sharing/")), #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date created", type date}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Date created"}, {{"Files by Day", each Table.RowCount(_), Int64.Type}}) in #"Grouped Rows" in SourceGlobal COVID/Coronavirus 19 Dashboard
Covid dashboard based on the JHCSSE Datasource. Along with my own calculations & analysis Source https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data/csse_covid_19_time_series eyJrIjoiZWMzYWMzNDUtZjJhZC00ZTllLWI1NGUtYzIwZWNlZTM0NDE3IiwidCI6ImZhZmQzNjI5LWJkYjYtNGVhOS1hMmQ5LWY2MjEwN2ViYTkwMSIsImMiOjEwfQ%3D%3DCOVID19 UK dash
United Kingdom Dashboard containing cases per UTLA & other breakdowns Sources: https://www.gov.uk/government/publications/covid-19-track-coronavirus-cases https://docs.google.com/spreadsheets/d/18YDwo8yR8oC_WwtYyFncGVSNAnxEddLZoOXtr_kDpC0/edit#gid=0 eyJrIjoiNDc5MjFjNmUtMmUxOS00YTk3LThlMjEtNjlmOTU3OTFmMjU0IiwidCI6ImZhZmQzNjI5LWJkYjYtNGVhOS1hMmQ5LWY2MjEwN2ViYTkwMSIsImMiOjEwfQ%3D%3DRe: Cannot Schedule refresh Azure SQL Database
Hi venal Thanks. Yes, it works with static Sql.Database("Server name","Database name") but not with parameterized server & DB which is the issue. from this link here Note Power BI currently does not support parameterized data source definitions, also known as dynamic data sources. For example, you can't parameterize the data access function Sql.Database("SqlServer01", "AdventureWorks"). If your dataset relies on dynamic data sources, Power BI informs you that it detected unknown or unsupported data sources. You must replace the parameters in your data access functions with static values if you want Power BI to be able to identify and connect to the data sources. For more information, see Troubleshooting unsupported data source for refresh. But clicking that link gives you no further info on how to fix other than static server name & database which is not a viable option for this dataset. I understand this could be a bit of a niche problem but if anyone has had this and has a solution I would be most grateful Error message here if this helps: Something went wrong There was an error when processing the data in the dataset. Please try again later or contact support. If you contact support, please provide these details. Data source error: Unable to refresh the model (id=7923608) because it references an unsupported data source. Cluster URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net Activity ID: ff09dc5c-42df-458a-97c7-7b0a060af6bc Request ID: 0e77e53b-7597-d328-b8dc-7363438cb002 Time: 2019-11-13 06:08:02ZRe: Cannot Schedule refresh Azure SQL Database
HI Lin I am not sure that helps. The source is SQL.Database, which is a supported source for data refresh. This is also an Azure SQL database so there is no need for a gateway. I gather that this is probably due to the functions but there is no documentation that I can find to help with this. Refresh works find on the .pbix fileCannot Schedule refresh Azure SQL Database
Hi I am currently getting the error message below when trying to refresh from the service (works fine on desktop): You can't schedule refresh for this dataset because the following data sources currently don't support refresh: (List of Queries, all from SQL Database) Query contains unsupported function. Function name: Sql.Database From the refresh history the error message is : Data source error: Unable to refresh the model () because it references an unsupported data source The purpose of the function is to append together mulitple databases & servers that have the same structure into one single table. e.g. advanced editor below: let Source = #"DB & Server", #"Changed Type" = Table.TransformColumnTypes(Source,{{"Server", type text}, {"DB", type text}}), #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "pbig MP", each #"pbig MP"([DB], [Server])), #"Expanded Query1" = Table.ExpandTableColumn(#"Invoked Custom Function", "pbig MP", {"Name", "Data", "Schema", "Item", "Kind"}, {"Name", "Data", "Schema", "Item", "Kind"}) in #"Expanded Query1" The Source "Db & Server" has two columns, DB & Server, and 4 rows with unique Db & server combinations. Has anyone had this issue before & managed to solve? advanced editor text of the function is: (DB as text, Serv as text) as table => let Source = Sql.Database(Serv, DB) in Source
