Power Query formulas

[Anonymous]

Hello Guys,

Need a help to find the function. I have a data table with full rows. I'm trying to pull the same data for last 1 week from today and not finding the correct code to fill.

[Jimmy801]

Hello @Anonymous 

I've troubles to follow you. It's that a filter or a custom function?

However use Date.AddDays -7 or Date.AddWeeks - 1 to get last week and use Date.From(DateTime.FixedLocalNow()) to get today. Maybe this is what you are looking for

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi Jimmy,

Thanks for your response. It's a custom function. I have tried the above, also Date.AddDays(DateTime.FixedLocalNow(),-7 . Here is the error.

[Jimmy801]

Hello @Anonymous 

about the Date.AddDays-function. it takes 2 parameters... the first is the date and the second are the days to shift. So the function has to look like this

Date.AddDays(Date.From(DateTime.FixedLocalNow()),-7)

The other thing I noticed is that the input parameter of your function of StartDate and EndDate is datatype text and in the first line you assign a date if its null. This could lead also to an error. 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hey,

I have made some changes, now below is the error.

Here is the query: any help would be appeciated;

let
xotGetCatalogDataFile = (Catalog as text, optional Criteria as text, optional StartDate as datetime, optional EndDate as datetime) =>
let
StartDate = if (StartDate = null) then Date.AddDays(DateTime.FixedLocalNow(),-7) else StartDate,
EndDate = if (EndDate = null) then DateTime.FixedLocalNow() else EndDate,
Criteria = if (Criteria = null) then "{}" else Criteria,
/*Add Details to Query*/
Request = "{""catalogname"":[""" & Catalog & """], ""startDate"":""" & StartDate & """, ""endDate"":""" & EndDate & """, ""companyName"":""" & Workspace & """, ""criteria"":[" & Criteria & "]}",
/*xotMakeJsonRequest*/
Source = Web.Contents(Server & "/restapi/powerquery/file", [Headers=[Authorization=AccessKey, #"content-type"="application/json"], Content=Text.ToBinary(Request),ManualStatusHandling={409,404,200,401},IsRetry = true]),
GetMetadata = Value.Metadata(Source),
GetResponseStatus = GetMetadata[Response.Status],
UID = if GetResponseStatus=200 then Json.Document(Source)[#"requestId"][#"$oid"] else Json.Document(Source)[message],
/*xotMakeJsonRequest*/
Start = xotGetStatus(UID, Workspace),
/*xotWaitForLink*/
MaxAttempts = 720, Seconds = 5,
Attempts = List.Generate( () =>
[Result = "Pending", Attempts = 1],
each Text.StartsWith([Result], "Pending") and [Attempts] < MaxAttempts,
each [Result = Function.InvokeAfter( () => try xotGetStatus(UID, Workspace) otherwise "/test", #duration(0,0,0,Seconds)), Attempts = [Attempts] + 1],
each xotGetStatus(UID, Workspace) ),
/*xotWaitForLink*/
Link = try List.Last(Attempts) otherwise xotGetStatus(Server, UID, Workspace, AccessKey),
Output = if Text.Length(UID) = 24 then if Text.StartsWith(Link, "/") then Csv.Document(Binary.Decompress(Web.Contents(Server & Link & "?companyName="& Workspace, [Headers=[#"Authorization"=AccessKey]]), Compression.GZip)) else List.Combine({Text.Split(Link, ","), {UID}}) else UID,
RawData = if Text.Length(UID) = 24 then if Text.StartsWith(Link, "/") then Table.PromoteHeaders(Output, [PromoteAllScalars=true]) else List.Combine({Text.Split(Link, ","), {UID}}) else UID,
#"Promoted Headers" = Table.TransformColumnTypes(RawData,{{"Variable Time", type datetime}, {"Variable Value", type number}})
/*#"Promoted Headers" = if Text.Length(UID) = 24 then if Text.StartsWith(Link, "/") then Table.PromoteHeaders(Output, [PromoteAllScalars=true]) else List.Combine({Text.Split(Link, ","), {UID}}) else UID*/
in
#"Promoted Headers"
in
xotGetCatalogDataFile

[Jimmy801]

Hello @Anonymous 

this now has nothing to do with the original question. The question was how to get current date and - 7 days. If the original post was answered I would ask you to mark the mark the post as solution that solved that issue.

The new issue you have because somewhere in your code you are trying to feed a list, where a table is expected. By the way, your code is full of custom functions, so impossible to understand where this error is caused. You should try to make some troubleshooting before you post this here, to understand where this error is caused. It seems also that this error is caused not only by one query but by multiple queries. What do they have in common?

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy