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"calculate estimated service date
I'm working on the following use case: It involves a service desk and the estimated date that a ticket will be processed. At any certain day, there will be an X amount of capacity per team to process tickets (eg 20 hrs on 2022-12-8, 15 on 2022-12-9 etc). Each ticket contains an estimated effort to complete it. The challenge is to estimate when a ticket will be picked up by the service desk based off the ticket rank and the available capacity. Tables: Capacity Team Date Capacity in hours a 2022-12-8 20 b 2022-12-8 30 a 2022-12-9 15 b 2022-12-9 25 Tickets (filtered status = Open) rank Assigned to Team Estimated effort in hours 1 a 10 2 a 10 3 b 30 4 a 10 5 b 10 Capacity table is connected to date table and team table. Ticket table is only connected to team table (since I need to calculate the estimated date still) Goal: In my example, tickets rank 1 and 2 will be processed on 2022-12-8 by team a, and ticket rank 4 will be processed on 2022-12-9. I've come as far to calculate on a per day basis (=capacity 8 hours per day) using Current Date as a starting point in the following measure: Estimated date = --calculate estimated effort in days var _remwork = DIVIDE (CALCULATE(SUM(Tickets[Estimated Effort]), FILTER(ALL(Tickets),Tickets[Rank]<SELECTEDVALUE(Tickets[Rank]))),8,0) var _currentDate = Today() var _networkingdays = FILTER(ALL(Datum),Datum[Datum]>_currentDate && Datum[Weekday] = "Yes") var _Datecomplete = TOPN(ROUND(_remwork,0),_networkingdays,Datum[Datum],asc) var _result = MAXX(_Datecomplete,Datum[Datum]) return _result I'm struggling to compare the actual capacity per given date with the cumulative estimated effort of all tickets. That means that in the first step, calculating the effort, I somehow have to incorporate the available capacity and when the cumulative estimated effort exceeds this for any given day, it will add a day to the estimated date and so on. What I can't seem to wrap my head around is that the capacity has assigned dates, but the tickets have not, making it hard to compare it on that dimension. Any idea is welcome (DAX, Power Query, both).
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.Find max for each group of rows
Hello. I have the problem - how to get column 3 according to Column 1, 2, and the value column (table is below) For the locations and names, I have to detect the max value that I will use in further calculations Not sure can it be done in M Query or DAX? Thanks Nice hot greeting from Croatia
JSON Data parsing issue
Hello, I am currently working with JSON data and facing an issue in parsing it in power bi. The JSON data I have is not consistent in length. It has a variable number of columns in each row, as well as there, are sublists(Old & New) in some rows. So when I parse the data some of the sublists of the data is stored as `Record` while other who is not having any sublists are parsed properly. As shown in the screenshot the one where the old and new value is present those are not parsed while other rows are converted into columns properly. I would like to parse the remaining records so that I could combine them using Dax as per my requirement in one column. Right now the columns are a mix of Records and individual values because of it I am not able to expand into new columns. It would be great if you could let me know a way I could do it in M Query or any other power bi feature. Any help would be appreciated!
How to create a rank column using a boolean value column, by occurence of 'TRUE' in ordered table?
I have a time indexed table with a `Group`, a `Category` , and a `event_bool` . I need to plot stuff against the boolean variable, because whenever `TRUE` value occurs it is an `event` in the timeline that needs to be analyzed one against the other. After the ranking is done, I also want to fill the ranking in a way that only the `Category` variable with value `foo` gets the rank value. Thus the event time range is "expanded" out based on `Category` variable. The last two columns are expected outputs. Edit: Ranking is to be done only when Cateogry='foo' Edit: sample pbix file: https://drive.google.com/file/d/1mPlsGJ1YR88YfyKkIgYBTIZuGyBDT38S/view?usp=sharing I have previously done `Expected_ranking_output` in python, and was planning to plug in the python script with power query; however, I read in the docs that you have make the dataset public if you want to python with power query (bemused by this). Not sure if my organization will allow that. Group Time_Index Category event_bool Some_Plotting_Value_Col Expected_ranking_output Expected_Filled_Output A 1 other FALSE 0.929 0 0 A 2 foo FALSE 0.929 0 1 A 3 foo FALSE 0.209 0 1 A 4 foo FALSE 0.191 0 1 A 5 foo TRUE 0.071 1 1 A 6 foo TRUE 0.507 1 1 A 7 foo TRUE 0.101 1 1 A 8 foo TRUE 0.661 1 1 A 9 foo FALSE 0.936 0 1 A 10 foo FALSE 0.611 0 1 A 11 foo TRUE 0.397 2 2 A 12 foo TRUE 0.677 2 2 A 13 foo TRUE 0.407 2 2 A 14 foo FALSE 0.473 0 2 A 15 foo FALSE 0.411 0 2 A 16 foo FALSE 0.012 0 2 A 17 bar TRUE 0.080 0 0 A 18 bar TRUE 0.149 0 0 A 19 bar TRUE 0.748 0 0 A 20 bar TRUE 0.812 0 0 B 1 foo FALSE 0.734 0 1 B 2 foo FALSE 0.917 0 1 B 3 foo TRUE 0.734 1 1 B 4 foo TRUE 0.755 1 1 B 5 foo TRUE 0.854 1 1Group table in terms of date column and specific value of another column
Hello all, I have a problem which sounds so easy but as I'm a newbie I can't manage it and need your expertise to help me please: As a sample you can have a look at the table below: I need to remove all the rows which have not Main Category value on their date (in this example I should remove rows 9,10,11), The main category value here is the beginning point for my process per day and if it does not exist in a day, I should remove all the other records on that specific date or ignore them could you please let me know how can I make a DAX code or M-query to fix it? as my data are thousands or millions, needs to have the fastest one. Many thanks
