Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
I am using below url for table count which can give table limit:
https://xxx.com/api/now/v1/stats/u_tap_request?sysparm_count=true
and using below url to fetch the data:
where we can put sysparm_offset=1 and sysparm_limit=100 - these 2 parameter for offset and limit value.
Now I have made 1 function to dynamically set the offset value:
(Offset as number) as table =>
let
Source = Json.Document(Web.Contents("https://xxx.com/api/now/table/u_tap_request?sysparm_display_value=false&sysparm_exclude_reference_li..." & Number.ToText(Offset) & "&sysparm_limit=100")),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"parent", "u_solution_complexity_date_time"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Value1",{"Name"})
in
#"Removed Columns"
now i want to load the whole table dynamically with each iteration 100 rows and have to make a table. So how can achieve that?
Hi,
I am really stuck here and dont know the code or logic how to iterate the table dynamically based on the total records. Please help me on that.
Hi @sandip ,
please check this sample query. It should give you some ideas how to tackle your task:
let
Source = List.Generate( ()=>
[Result = Json.Document(Web.Contents("https://pokeapi.co/api/v2/ability/")), Counter=0, Next = Json.Document(Web.Contents("https://pokeapi.co/api/v2/ability/"))[next]],
each [Next] <> null,
each [
Next = [Result][next],
Result = let
next = [Result][next],
offset = Text.BetweenDelimiters( next, "offset=", "&"),
limit = Text.AfterDelimiter(next, "limit="),
StartWithEmptyTable = Json.Document(Web.Contents("https://pokeapi.co/api/v2/ability/?offset=0&limit=20", [Query=[offset=Text.From(offset), limit=Text.From(limit)]]))
in
StartWithEmptyTable,
Counter = [Counter] + 1
]
),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Result", "Counter"}, {"Result", "Counter"}),
#"Expanded Result" = Table.ExpandRecordColumn(#"Expanded Column1", "Result", {"count", "next", "previous", "results"}, {"count", "next", "previous", "results"}),
#"Expanded results" = Table.ExpandListColumn(#"Expanded Result", "results"),
#"Expanded results1" = Table.ExpandRecordColumn(#"Expanded results", "results", {"name", "url"}, {"name", "url"})
in
#"Expanded results1"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi,
I have written the below code:
in getData function as below:
= (Offset as number) as table =>
let
Source = Json.Document(Web.Contents("https://amerisourcebergentest.service-now.com/api/now/table/u_tap_request?sysparm_display_value=fals..." & Number.ToText(Offset) & "&sysparm_limit=99")),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"parent", "u_solution_complexity_date_time", "u_supplier_name", "watch_list", "upon_reject", "requested_for", "sys_updated_on", "approval_history" }, {"Value.parent", "Value.u_solution_complexity_date_time", "Value.u_supplier_name", "Value.watch_list", "Value.upon_reject", "Value.requested_for", "Value.sys_updated_on", "Value.approval_history", "Value.skills", "Value.number", "Value.u_solution_compllexity_not_required", "Value.state", "Value.u_approved_funding_confirmed_by_finance", "Value.sys_created_by"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Value1",{"Name"})
in
#"Removed Columns"
But when I execute the below code it gives me error and it seems to me that it is doing extra looping :
error message:
let
Source = List.Generate(()=>
[Result = try getData(1) otherwise null, Page=1],
each [Result] <> null,
each [Result = try getData([Page]+99) otherwise null, Page=[Page]+99],
each [result]
)
in
Source
Hi Sandip, i have the same task, i hope you hvae fighred out, please can you send the whole code? thank you
Hi @sandip ,
this doesn't look like an error message to me.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |