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
Expand Dates in Large Data Set - PowerQuery and Dax
I have a large data set in a proper star schema. However, i'm struggling with some reports out of it. I'll explain what i'm currently doing, and hopefully someone can tell me a better way (or more elegant) way to accomplish something similar. Data set: There's many more tables that have proper relationships (Aprox 30 more), but for the purpose of this exercise, that's all we need. Most of the work happens in the ResourceActualDetail table it looks like this (with some columns deleted): ResourceAllocationId AllocationPercentage EndDate StartDate 794 1 4/1/2018 0:00 2/25/2018 0:00 795 1.15 3/4/2018 0:00 2/25/2018 0:00 795 1.0875 3/11/2018 0:00 3/4/2018 0:00 795 1 4/1/2018 0:00 3/11/2018 0:00 796 1.05 3/4/2018 0:00 2/25/2018 0:00 797 0.725 3/4/2018 0:00 2/25/2018 0:00 1531 1 3/11/2018 0:00 2/25/2018 0:00 1721 1 4/1/2018 0:00 2/25/2018 0:00 Desired Output: ResourceAllocationId AllocationPercentage EndDate StartDate Date 794 1 4/1/2018 0:00 2/25/2018 0:00 2/26/2018 0:00 794 1 4/1/2018 0:00 2/25/2018 0:00 3/4/2018 0:00 794 1 4/1/2018 0:00 2/25/2018 0:00 … 794 1 4/1/2018 0:00 2/25/2018 0:00 4/1/2018 0:00 Notice the 7 day increments. And the same for every ResourceAllocationID so that I can graph it like so: (error in Graph dates, should be with 7 day increments). Where there is a different line for each Resource (connected through relationships to ResourceAllocation and ResourceActualDetail). Even if we can get it to a point where we can do that for each ResourceAllocationId I can figure out the rest 🙂 Current Approach: These data tables are quite large already(~1M rows) , and my approach only has bloated that. Trying to do everything as close to the source, this what I came up with: Let Source=PowerBI.Dataflows(null), ... #"Changed Type" = Table.TransformColumnTypes(ResourceActualDetail1,{{"EndDate", type number}, {"StartDate", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Dates", each List.Numbers([StartDate],([EndDate]-[StartDate])/7,7)), #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Dates"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type datetime}, {"EndDate", type datetime}, {"StartDate", type datetime}}) in #"Changed Type1" Essentially, I change the StartDate and EndDate fields to numeric Values and then I calculate the beginning of each week before i switch back all those values to Date type. This part of the query doesn't fold, so I cannot take the advantage of the PowerFlow to do it. It also makes each refresh very slow (should be refresed multiple times a day). Any suggestions or ideas?? Obviously looking for the most efficient way to achieve this so that it won't take many resources (I do have a premium instance). Thanks!Find repeat values based on criteria
I’m hoping you all might be able to help me. I’m fairly experienced in Power BI but can’t figure this issue out. I have a list of dates/times and phone numbers and departments (and some other data as well). What I am trying to do is figure out if a certain department is better at answer questions the first time, resulting in the fewest call backs. For example, let’s say phone number 1234567890 called and was routed to Department Y. Then a week later they called again, and the call was routed to Department X. I want this to be counted as a repeat caller for Department Y (because they originally contacted Department Y first, so it’s department Y’s “fault” they are calling again). Happy to clarify more if needed. I also don’t care if this is written with Dax or M. I also just made a breakthrough where I was able to have a running count of whether the call was 1st, 2nd, 3rd, etc. Time the phone number has called. So now I just need something that says: If the number is greater than 1, then find the first instance of the number and return which department answered the call. Thank you in advance.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
