Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.