cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dude95
Resolver I
Resolver I

REST API - Ignore function when parameter is empty

API from Asana.  Trying to pull in weekly status reports for projects.  Works great when there is at least 1 status report, but when no status reports have been created the status report GUID is null and the function to get the details returns an error (can't convert value null to type text) because the API doesn't return a record. 

 

Fairly newbie as it comes to Power Query - can figure out how to ignore the function when there isn't a status report to get??  Is it an if statment (what would that look like?)  I'm struggling with the syntax if this is the answer.  Something else?

 

let
 //   if Status_GUID = null then Source = null else 
    Source = (Status_GUID as text) => let
    Source = Json.Document(Web.Contents("https://app.asana.com/api/1.0/project_statuses/" & Status_GUID, [Headers=[#"Content-Type"="application/json", Authorization="Bearer xxxxxxxxxxxxxxxx"]])),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"gid", "archived", "color", "created_at", "current_status", "custom_fields", "custom_field_settings", "default_view", "due_on", "due_date", "followers", "is_template", "members", "modified_at", "name", "notes", "owner", "permalink_url", "public", "resource_type", "start_on", "team", "workspace"}, {"Value.gid", "Value.archived", "Value.color", "Value.created_at", "Value.current_status", "Value.custom_fields", "Value.custom_field_settings", "Value.default_view", "Value.due_on", "Value.due_date", "Value.followers", "Value.is_template", "Value.members", "Value.modified_at", "Value.name", "Value.notes", "Value.owner", "Value.permalink_url", "Value.public", "Value.resource_type", "Value.start_on", "Value.team", "Value.workspace"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Value",{{"Value.created_at", type datetime}})
in
    #"Changed Type"
in
    Source

 

1 ACCEPTED SOLUTION
Stachu
Community Champion
Community Champion

try this:

(Status_GUID as text) => let
    Source = Json.Document(Web.Contents("https://app.asana.com/api/1.0/project_statuses/" & Status_GUID, [Headers=[#"Content-Type"="application/json", Authorization="Bearer xxxxxxxxxxxxxxxx"]])),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"gid", "archived", "color", "created_at", "current_status", "custom_fields", "custom_field_settings", "default_view", "due_on", "due_date", "followers", "is_template", "members", "modified_at", "name", "notes", "owner", "permalink_url", "public", "resource_type", "start_on", "team", "workspace"}, {"Value.gid", "Value.archived", "Value.color", "Value.created_at", "Value.current_status", "Value.custom_fields", "Value.custom_field_settings", "Value.default_view", "Value.due_on", "Value.due_date", "Value.followers", "Value.is_template", "Value.members", "Value.modified_at", "Value.name", "Value.notes", "Value.owner", "Value.permalink_url", "Value.public", "Value.resource_type", "Value.start_on", "Value.team", "Value.workspace"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Value",{{"Value.created_at", type datetime}}),
    Result = if Status_GUID = null then null else #"Changed Type"
in
    Result


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

3 REPLIES 3
Stachu
Community Champion
Community Champion

there is if:
https://docs.microsoft.com/en-us/powerquery-m/m-spec-conditionals

as well as try:
https://docs.microsoft.com/en-us/powerquery-m/errors



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Still struggling here on how to implement against a function that will bring in a list.  I've got the if statement commented out above becuase it gives a syntax error.  Any advice on how I'm trying to implement the if statement incorrectly?

Stachu
Community Champion
Community Champion

try this:

(Status_GUID as text) => let
    Source = Json.Document(Web.Contents("https://app.asana.com/api/1.0/project_statuses/" & Status_GUID, [Headers=[#"Content-Type"="application/json", Authorization="Bearer xxxxxxxxxxxxxxxx"]])),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"gid", "archived", "color", "created_at", "current_status", "custom_fields", "custom_field_settings", "default_view", "due_on", "due_date", "followers", "is_template", "members", "modified_at", "name", "notes", "owner", "permalink_url", "public", "resource_type", "start_on", "team", "workspace"}, {"Value.gid", "Value.archived", "Value.color", "Value.created_at", "Value.current_status", "Value.custom_fields", "Value.custom_field_settings", "Value.default_view", "Value.due_on", "Value.due_date", "Value.followers", "Value.is_template", "Value.members", "Value.modified_at", "Value.name", "Value.notes", "Value.owner", "Value.permalink_url", "Value.public", "Value.resource_type", "Value.start_on", "Value.team", "Value.workspace"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Value",{{"Value.created_at", type datetime}}),
    Result = if Status_GUID = null then null else #"Changed Type"
in
    Result


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors