Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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 🙂

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 🙂

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 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors