Forum Discussion

PavelVitek's avatar
PavelVitek
New Member
4 years ago
Solved

REST.API Data retrieval operations

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 qu...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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.

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
4 years ago

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's avatar
PavelVitek
New Member
4 years ago

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's avatar
    v-jingzhang
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      PavelVitek
      New Member
      4 years ago

      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