POST request with Power Query fails auth but needs to be Anonymous
The Web.Contents function needs to specify content for making a POST request, but can`t get auth right. I pass the credentials (APIKey) and it works on Postman, but if I do it anonymous in PB it fails saying that can`t verify the credentials, but if I change it to basic auth PB tells me that the query needs to be from anonymous source. The code looks like this, the main part ends at JsonResponse, I don't think that the transformation of data matters for this problem.
Grouping dates into fortnights
Hi everyone, I'm looking for a solution that will help in grouping dates into fortnights, irregardless of month or year. I'll attach an image to help understand what I am meaning. It wouldn't matter if the column started at calendar start or a specific date. The date table I have is just an invoked funtion in M. Hopefully someone can help, I'm not sure what other information I could provide that would be helpful here.How can I show latest Price, with DAX?
Hello, I have some sample data below the prompt. I have to create a dax measure from this, any thoughts? Thank you! Customer's Latest Price for specific item in the last 180 days If latest price is less than the Retail Base Price (which is column from seperate tabe), then show the Retail Base price. If price is blank (as having no sales history in last 180 days), then show Retail List Price. If customer's latest price (or product of this formula) is greater than Retail List Price (column from seperate table), show Retail List Price. Customer_No Item_No Unit_Price Invoice_No Due_Date C1101 x11 $275.00 6867 4/2/2021 C1099 co22 $250.00 6868 4/2/2021 C1101 x14 $259.00 6842 3/30/2021 C1099 c200 $189.00 6821 3/26/2021
M code for a DAX expression
Hi everyone, I need to transform this DAX expression in M: Week= YEAR(Calendar[Date]+26-WEEKNUM(Calendar[Date],21)) & "-" & IF(WEEKNUM(Calendar[Date],21)>=10, WEEKNUM(Calendar[Date],21), "0" & WEEKNUM(Calendar[Date],21)) which calculates the week of the year followed by the year for each date of my calendar, according to the European system: Can you help me? 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!
