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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors