Query contains unsupported function. Function name: Web.Contents for web API
Hi, I have following code I am using to get data from WEB API, I am getting error when I tried to schedule refresh in Powerbi Service. I am not sure what needs to be changed in code to get it work. Error: "You can't schedule refresh for this dataset because the following data sources currently don't support refresh: Data source for dtc_noWorkOrder Data source for dtc_rejected Discover Data Sources Query contains unsupported function. Function name: Web.Contents" M Query: let BaseUrl = "https://forms.logiforms.com/api/1.0/form/371533/data?", InfoUrl = "https://forms.logiforms.com/api/1.0/form/371533/", Token = "YXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX", EntitiesPerPage = 100, GetJson = (Url) => let Options = [Headers=[Authorization = "Basic " & Token ]], RawData = Web.Contents(Url, Options), Json = Json.Document(RawData) in Json, GetEntityCount = () => let Url = InfoUrl, Json = GetJson(Url), Count = Json[data][form][submissions] in Count, GetPage = (Index) => let Skip = "page=" & Text.From(Index), Url = BaseUrl & Skip, Json = GetJson(Url), Value = Json[data][records] in Value, EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }), PageCount = Number.RoundUp(EntityCount / EntitiesPerPage), PageIndices = { 1 .. PageCount }, Pages = List.Transform(PageIndices, each GetPage(_)), Entities = List.Union(Pages), #"Converted to Table" = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"datesubmitted", "Timecard_RecordID", "lfuuid", "User_Name", "User_RecordID", "Rejection_Reason", "User_Functional_Area", "RecordID", "Rejection_Code"}, {"datesubmitted", "Timecard_RecordID", "lfuuid", "User_Name", "User_RecordID", "Rejection_Reason", "User_Functional_Area", "RecordID", "Rejection_Code"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"datesubmitted", type datetime}, {"Timecard_RecordID", Int64.Type}, {"lfuuid", type text}, {"User_Name", type text}, {"User_RecordID", Int64.Type}, {"Rejection_Reason", type text}, {"Rejection_Code", type text}, {"RecordID", Int64.Type}, {"User_Functional_Area", type text}}), #"Reordered Columns" = Table.ReorderColumns(#"Changed Type",{"datesubmitted", "lfuuid", "RecordID", "Timecard_RecordID", "User_Name", "User_RecordID", "User_Functional_Area", "Rejection_Reason", "Rejection_Code"}), #"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "Rejection_Description", each ( if [Rejection_Code] = "Z001" then "Wrong Work Order" else if [Rejection_Code] = "Z002" then "Wrong Segment" else if [Rejection_Code] = "Z003" then "Time Not Approved" else if [Rejection_Code] = "Z004" then "Incorrect Day / Time" else "" )) in #"Added Conditional Column"
Dynamic M Query Parameters don't work on reporting server.
Hi everyone, We have a dashboard with Dynamic M Query Parameters as a date filters. The dashboard works find locally on the desktop version, the problem arises when we upload it to the reporting server. It'll open but the parameters won't work. Whenever we change the date filter, the data stays the same. Database: Teradata Version: Sep 2022 I'd appreciate any help. Thank you.M code works in Power BI Desktop but blank when refreshing in Server (on-prem)
Good morning! I am posting this on behalf of sqlryan. If you have any suggestions or information, it would be greatly appreciated! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ I have a cleanup function to remove HTML tags from a text field - it works on Desktop, but once I publish to Report Server and schedule a refresh, the function returns blank instead of the correct value. The refresh doesn't fail and there's no error message - it just returns a blank string. Troubleshooting was tough because you can't view the dataset on Server and we just had a blank field, but I reproduced it in the attached file (contains field w/HTML, and then an example of the broken function - "bad" - and one that works - "good"). It's not a browser issue - Chrome, Edge, and IE all show the same thing - and it displays accurately on the Report Server until we do a scheduled data refresh - then the issue shows itself. Any idea what's going on here? I'm using the May 2019 Desktop release (tied to our production servers) and I see the same behavior in both the May 2019 and September 2019 Report Server releases. We can use a different function as a work-around, but wanted to see why this one specifically isn't working. Here's a downloadable example PBIX (with the Excel data file containing basic data so a server refresh can be set up) that shows both functions in action: http://sqlryan.com/Downloads/HtmlRemovalTest.zip These are the two functions - they both remove HTML tags in desktop, but this one returns blank on the server: let Source = (TextContainingHTML as any) => let Source = TextContainingHTML, Result = if TextContainingHTML=null then null else Html.Table(Source, {{"text",":root"}})[text]{0} in Result in Source Where this one has the same (working) result in the desktop, but it also works on the server: let func = (HTML) => let Check = if Value.Is(Value.FromText(HTML), type text) then HTML else "", Source = Text.From(Check), SplitAny = Text.SplitAny(Source,"<>"), ListAlternate = List.Alternate(SplitAny,1,1,1), ListSelect = List.Select(ListAlternate, each _<>""), TextCombine = Text.Combine(ListSelect, "") in TextCombine in func
