Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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.
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.
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
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.
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
55 | |
42 | |
28 | |
22 |