REST.API Data retrieval operations

[PavelVitek]

Hi,

I am triing to prepare data for company reporting upon our project system using REST.API to retrieve data.

I was able to retrieve first page of datas from the source. So I proceed to make query to loop through all pages and retrieve data, collect them and produce them. However, there is slight issue.

When I do the query for one page, it retrieves all data that i need:
Retrieval of data:

Transforming into table:

Opening list:

And the most important thing - opening results:

M Query looks like this:

let
    Zdroj = Json.Document(Web.Contents("URI", [Headers=[Authorization="AuthHeader"]])),
    #"Převedené na tabulku" = Table.FromRecords({Zdroj}),
    #"Změněný typ" = Table.TransformColumnTypes(#"Převedené na tabulku",{{"page", Int64.Type}, {"per", Int64.Type}, {"prev_page", type any}, {"next_page", type text}, {"total_pages", Int64.Type}, {"total_results", Int64.Type}, {"total_writable_results", Int64.Type}, {"total_virtuals", Int64.Type}, {"counters", type any}, {"results", type any}, {"unread_object_ids", type any}}),
    #"Rozbalené results" = Table.ExpandListColumn(#"Změněný typ", "results"),
    #"Rozbalené results1" = Table.ExpandRecordColumn(#"Rozbalené results", "results", {"id", "name", "description", "start_time", "end_time", "planned_hours", "planned_incomes", "planned_expenses", "total_hours_helper", "tracked_time_helper", "native_planned_incomes", "total_incomes_helper", "native_planned_expenses", "total_expenses_helper", "earnings", "earnings_price", "budget_progress", "incomes_progress", "expenses_progress", "hours_progress", "currency", "project_id", "company_id", "user_id", "target_user_id", "priority", "task_type_id", "task_status_id", "finished", "progress", "get_progress_old", "get_progress", "user_ids", "template", "inherited_template", "repeatable", "repeater_id", "repeat_kind", "repeat_count", "repeat_weekdays", "repeat_monthdays", "repeat_end", "repeat_end_date", "repeat_end_count", "repeat_from", "trash", "trashed_at", "privacy", "created_at", "updated_at", "url", "api_url", "tags", "position", "get_todos_stats", "planned_tracked_time", "comment_email", "address", "company_name", "company_url", "project_name", "project_url", "task_status_name", "following", "task_type_name"}, {"results.id", "results.name", "results.description", "results.start_time", "results.end_time", "results.planned_hours", "results.planned_incomes", "results.planned_expenses", "results.total_hours_helper", "results.tracked_time_helper", "results.native_planned_incomes", "results.total_incomes_helper", "results.native_planned_expenses", "results.total_expenses_helper", "results.earnings", "results.earnings_price", "results.budget_progress", "results.incomes_progress", "results.expenses_progress", "results.hours_progress", "results.currency", "results.project_id", "results.company_id", "results.user_id", "results.target_user_id", "results.priority", "results.task_type_id", "results.task_status_id", "results.finished", "results.progress", "results.get_progress_old", "results.get_progress", "results.user_ids", "results.template", "results.inherited_template", "results.repeatable", "results.repeater_id", "results.repeat_kind", "results.repeat_count", "results.repeat_weekdays", "results.repeat_monthdays", "results.repeat_end", "results.repeat_end_date", "results.repeat_end_count", "results.repeat_from", "results.trash", "results.trashed_at", "results.privacy", "results.created_at", "results.updated_at", "results.url", "results.api_url", "results.tags", "results.position", "results.get_todos_stats", "results.planned_tracked_time", "results.comment_email", "results.address", "results.company_name", "results.company_url", "results.project_name", "results.project_url", "results.task_status_name", "results.following", "results.task_type_name"}),
    #"Přidané: Vlastní" = Table.AddColumn(#"Rozbalené results1", "results.tracked_time_helper_hours", each [results.tracked_time_helper]/3600),
    #"Přejmenované sloupce" = Table.RenameColumns(#"Přidané: Vlastní",{{"results.id", "1.result.ID"}, {"results.name", "1.task.name"}}),
    #"Odebrané sloupce" = Table.RemoveColumns(#"Přejmenované sloupce",{"page", "per", "prev_page", "next_page", "total_pages", "total_results", "total_writable_results", "total_virtuals", "counters", "results.start_time", "results.end_time", "results.planned_hours", "results.planned_incomes", "results.planned_expenses", "results.total_hours_helper"}),
    #"Přejmenované sloupce1" = Table.RenameColumns(#"Odebrané sloupce",{{"results.tracked_time_helper_hours", "Vykázaných hodin"}, {"results.company_name", "Název Společnosti"}, {"1.task.name", "Název úkolu"}, {"results.project_name", "Název projektu"}})
in
    #"Přejmenované sloupce1"

When I try to use it with loop, I get all columns I needed except for those loaded in step "And the most important thing - opening results:"

let
    baseuri = "URI",
    headers = [Headers=[Authorization="Token"]],

    initReq = Json.Document(Web.Contents(baseuri, headers)),
   // initDeq = Table.FromRecords({initReq}),
   // Step1 = Table.ExpandRecordColumn(initDeq, "counters", {"total", "income", "expense", "income_expense"}, {"counters.total", "counters.income", "counters.expense", "counters.income_expense"}),
     initData = initReq[results],
     initNumber = initReq[page],
    //We want to get data = {lastNPagesData, thisPageData}, where each list has the limit # of Records, 
    //then we can List.Combine() the two lists on each iteration to aggregate all the records. We can then
    //create a table from those records
    
gather = (data as list, uri) =>
        let
            //get new offset from active uri
            
        newOffset = Json.Document(Web.Contents(uri, headers))[next_page],
            
           
            //build new uri using the original uri so we dont append offsests
            newUri = "URI"?page=" & Text.BetweenDelimiters(newOffset, "page=", "&")  &"&per=20",
            //get new req & data
            
            newReq = Json.Document(Web.Contents(newUri, headers)),
            newdata = newReq[results],
            //add that data to rolling aggregate
            data = List.Combine({data, newdata}),
            //if theres no next page of data, return. if there is, call @gather again to get more data
            check = if newReq[next_page] = null then data else @gather(data, newUri)
        in check,
    //before we call gather(), we want see if its even necesarry. First request returns only one page? Return.
    outputList = if initReq[next_page] = null then initData else gather(initData, baseuri),
    //then place records into a table. This will expand all columns available in the record.

 
    #"Převedené na tabulku" = Table.FromRecords(outputList),
    #"Rozbalené results" = Table.ExpandListColumn( #"Převedené na tabulku", "results"),
    #"Rozbalené results1" = Table.ExpandRecordColumn(#"Rozbalené results1", "results", {"id", "name",  "start_time", "end_time", "planned_hours", "planned_incomes", "planned_expenses", "total_hours_helper", "tracked_time_helper", "native_planned_incomes", "total_incomes_helper", "native_planned_expenses", "total_expenses_helper", "earnings", "earnings_price", "budget_progress", "incomes_progress", "expenses_progress", "hours_progress", "currency", "project_id", "company_id", "user_id", "target_user_id", "priority", "task_type_id", "task_status_id", "finished", "progress", "get_progress_old", "get_progress", "user_ids", "template", "inherited_template", "repeatable", "repeater_id", "repeat_kind", "repeat_count", "repeat_weekdays", "repeat_monthdays", "repeat_end", "repeat_end_date", "repeat_end_count", "repeat_from", "trash", "trashed_at", "privacy", "created_at", "updated_at", "url", "api_url", "tags", "position", "get_todos_stats", "planned_tracked_time", "comment_email", "address", "company_name", "company_url", "project_name", "project_url", "following", "task_type_name", "task_status_name"}, {"results.id", "results.name", "results.description", "results.start_time", "results.end_time", "results.planned_hours", "results.planned_incomes", "results.planned_expenses", "results.total_hours_helper", "results.tracked_time_helper", "results.native_planned_incomes", "results.total_incomes_helper", "results.native_planned_expenses", "results.total_expenses_helper", "results.earnings", "results.earnings_price", "results.budget_progress", "results.incomes_progress", "results.expenses_progress", "results.hours_progress", "results.currency", "results.project_id", "results.company_id", "results.user_id", "results.target_user_id", "results.priority", "results.task_type_id", "results.task_status_id", "results.finished", "results.progress", "results.get_progress_old", "results.get_progress", "results.user_ids", "results.template", "results.inherited_template", "results.repeatable", "results.repeater_id", "results.repeat_kind", "results.repeat_count", "results.repeat_weekdays", "results.repeat_monthdays", "results.repeat_end", "results.repeat_end_date", "results.repeat_end_count", "results.repeat_from", "results.trash", "results.trashed_at", "results.privacy", "results.created_at", "results.updated_at", "results.url", "results.api_url", "results.tags", "results.position", "results.get_todos_stats", "results.planned_tracked_time", "results.comment_email", "results.address", "results.company_name", "results.company_url", "results.project_name", "results.project_url", "results.following", "results.task_type_name", "results.task_status_name"}),

   #"Přidané: Vlastní" = Table.AddColumn(#"Rozbalené results1", "tracked_time_helper_hours", each [results.tracked_time_helper]/3600),
    #"Odebrané sloupce" = Table.RemoveColumns(#"Přidané: Vlastní",{"tracked_time_helper_hours"})
in
    #"Odebrané sloupce"

This is just a sample of code I tried... It is working until step "Rozbalené results" and "Rozbalené Results1". The output list is correct set of data in list format:

However when I do the Table.FromRecords, some columns are missing, that are not missing in the original solution.

I was thinking that I could use Table.ExpandListColumn, however the outcome of gathering data is function containing records only.

Is there a way how to transform this "List" of records to list in column, so I could achieve similiar outcome as for the first query using Table.ExpandListColumn? 
Maybe my terminology is not clear, so I am sorry for that as I wasnt working with PBI for years.

Any suggestions are more than welcomed as well as maybe pointing me in right direction if this is not the one!

Cheers!
Kind regards,
Pavel

[v-jingzhang]

Hi @PavelVitek 

It seems you need to use Table.ExpandRecordColumn to expand the list of records. This is also the function you used in #"Rozbalené results1" step in your original query. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[PavelVitek]

Hi @v-jingzhang ,

Thank you for your suggestion, it is appreciated.

However this wont work.
If I use it on data list of records, I will get the error that Value of type List can not be transfered to type Table.
If I use it after Table.FromRecords, there is no such "RecordColumn" anymore.

Kind regards,

Pavel

[v-jingzhang · Accepted solution]

Hi @PavelVitek 

First convert the list of records to table. 

Then expand the column of records. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[PavelVitek]

Hi @v-jingzhang ,

Thank you, for your guidance. This worked!

I am posting the M query steps for anyone, that would wonder about it in future.

 #"Převedené na tabulku" = Table.FromList(outputList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Přejmenované sloupce" = Table.RenameColumns(#"Převedené na tabulku",{{"Column1", "results"}}),
    #"Rozbalené results" = Table.ExpandRecordColumn(#"Přejmenované sloupce", "results", {"id", "name", "description", "start_time", "end_time", "planned_hours", "planned_incomes", "planned_expenses", "total_hours_helper", "tracked_time_helper", "native_planned_incomes", "total_incomes_helper", "native_planned_expenses", "total_expenses_helper", "earnings", "earnings_price", "budget_progress", "incomes_progress", "expenses_progress", "hours_progress", "currency", "project_id", "company_id", "user_id", "target_user_id", "priority", "task_type_id", "task_status_id", "finished", "progress", "get_progress_old", "get_progress", "user_ids", "template", "inherited_template", "repeatable", "repeater_id", "repeat_kind", "repeat_count", "repeat_weekdays", "repeat_monthdays", "repeat_end", "repeat_end_date", "repeat_end_count", "repeat_from", "trash", "trashed_at", "privacy", "created_at", "updated_at", "url", "api_url", "tags", "position", "get_todos_stats", "planned_tracked_time", "comment_email", "address", "company_name", "company_url", "project_name", "project_url", "following", "task_type_name", "task_status_name"}, {"results.id", "results.name", "results.description", "results.start_time", "results.end_time", "results.planned_hours", "results.planned_incomes", "results.planned_expenses", "results.total_hours_helper", "results.tracked_time_helper", "results.native_planned_incomes", "results.total_incomes_helper", "results.native_planned_expenses", "results.total_expenses_helper", "results.earnings", "results.earnings_price", "results.budget_progress", "results.incomes_progress", "results.expenses_progress", "results.hours_progress", "results.currency", "results.project_id", "results.company_id", "results.user_id", "results.target_user_id", "results.priority", "results.task_type_id", "results.task_status_id", "results.finished", "results.progress", "results.get_progress_old", "results.get_progress", "results.user_ids", "results.template", "results.inherited_template", "results.repeatable", "results.repeater_id", "results.repeat_kind", "results.repeat_count", "results.repeat_weekdays", "results.repeat_monthdays", "results.repeat_end", "results.repeat_end_date", "results.repeat_end_count", "results.repeat_from", "results.trash", "results.trashed_at", "results.privacy", "results.created_at", "results.updated_at", "results.url", "results.api_url", "results.tags", "results.position", "results.get_todos_stats", "results.planned_tracked_time", "results.comment_email", "results.address", "results.company_name", "results.company_url", "results.project_name", "results.project_url", "results.following", "results.task_type_name", "results.task_status_name"})

Kind regards,

Pavel