Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hand Authored Queries

 

Hi Can I ask for help in changing the query below to make it work for schedule refresh? I've been going around in the community and web but really can't find a fix for my problem. Thank you so much!

 

let
    baseuri = "https://app.asana.com/api/1.0/projects?limit=100&workspace=*****************&opt_fields=team,name,owner,current_status,due_on,start_on,created_at,modified_at,public,members,custom_fields,custom_field_settings,color,notes,html_notes,workspace,archived",
    headers = [Headers=[#"Content-Type"="application/json", Authorization="Bearer 1/**************************"]],

    initReq = Json.Document(Web.Contents("https://app.asana.com/api/1.0/projects?limit=100&workspace=*****************&opt_fields=team,name,owner,current_status,due_on,start_on,created_at,modified_at,public,members,custom_fields,custom_field_settings,color,notes,html_notes,workspace,archived", headers)),
    initData = initReq[data],
    gather = (data as list, uri) =>
        let
            newOffset = Json.Document(Web.Contents(uri, headers))[next_page][offset],
            newUri = baseuri & "&offset=" & newOffset,
            newReq = Json.Document(Web.Contents(newUri, headers)),
            newdata = newReq[data],
            data = List.Combine({data, newdata}),
            check = if newReq[next_page] = null then data else @gather(data, newUri)
        in check,
    outputList = if initReq[next_page] = null then initData else gather(initData, baseuri),
    expand = Table.FromRecords(outputList),
    
    #"Changed Type" = Table.TransformColumnTypes(expand,{{"created_at", type datetime}, {"modified_at", type datetime}, {"due_on", type date}, {"start_on", type date}}),
    #"Expanded team" = Table.ExpandRecordColumn(#"Changed Type", "team", {"gid"}, {"team.gid"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded team",{ "workspace"}),
    #"Expanded owner" = Table.ExpandRecordColumn(#"Removed Columns", "owner", {"gid"}, {"owner.gid"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded owner",{"current_status", "custom_fields", "custom_field_settings", "members", "html_notes",  "color"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"gid", "created_at", "modified_at", "start_on", "due_on", "name", "notes", "owner.gid", "team.gid"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"archived", type text}, {"public", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","true","Yes",Replacer.ReplaceText,{"archived", "public"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","false","No",Replacer.ReplaceText,{"archived", "public"})
in
    #"Replaced Value1"

 

10 Replies