Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a column with unique IDs.
I need to pass these IDs as part of the RelativePath in an API call using List.Generate() to return paginated results (offset,limit)
In OOP this would be something similar to: "for every ID, perform the GET request"
When I run the script below, the result is a list with ERROR as the record. If I expand the column, only the first ID returns the results and the rest ERRORs.
Here is my M script:
let
//This is the column with team Ids
Source =
Json.Document(
Web.Contents("https://api.dashpivot.com/",
[
RelativePath = "companies/projects/teams/all",
Headers=[
Authorization="JWT xxxxxxx"
]
]
)
),
Source1 = Source{0},
projects = Source1[projects],
#"Converted to Table" = Table.FromList(projects, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"teams"}, {"Column1.teams"}),
#"Expanded Column1.teams" = Table.ExpandListColumn(#"Expanded Column1", "Column1.teams"),
#"Expanded Column1.teams1" = Table.ExpandRecordColumn(#"Expanded Column1.teams", "Column1.teams", {"id"}, {"id"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1.teams1",{{"id", type text}}),
Startdate = Date.AddDays(
DateTime.Date(
DateTime.AddZone(DateTime.LocalNow(),10)),
-#"Start Days"
),
Enddate = Date.AddDays(
DateTime.Date(
DateTime.AddZone(DateTime.LocalNow(),10)),
#"End Days"
),
//Adding the column with List.Generate to iterate over the IDs
#"Added Custom" = Table.AddColumn(#"Changed Type", "Data", each
List.Generate( () =>
[
Result=
try Json.Document(
Web.Contents(
"https://api.dashpivot.com/v1/",
[
RelativePath = "teams/"&[id]&"/photos?",
Query = [
limit="120",
offset=Number.ToText(OffsetValue),
startDate= Date.ToText(Startdate,"yyyy-MM-dd")&"T14:00:00.000Z",
endDate= Date.ToText(Enddate,"yyyy-MM-dd")&"T13:59:00.000Z"
],
Headers=[
Authorization="JWT xxxxx"
]
]
)
)otherwise try null, Offset = OffsetValue],
each not List.IsEmpty([Result]),
each
[
Result=
try Json.Document(
Web.Contents(
"https://api.dashpivot.com/v1/",
[
RelativePath = "teams/"&[id]&"/photos?",
Query = [
limit="120",
offset=Number.ToText([Offset]+120),
startDate= Date.ToText(Startdate,"yyyy-MM-dd")&"T14:00:00.000Z",
endDate= Date.ToText(Enddate,"yyyy-MM-dd")&"T13:59:00.000Z"
],
Headers=[
Authorization="JWT xxxxxx"
]
]
)
)otherwise try null, Offset = [Offset]+120],
each [Result]
)
)
Solved! Go to Solution.
Hi @MKE ,
unfortunately List.Generate can be a bit hard to debug. But it could be an ambiguity-problem here, as you are using it in a Table.AddColumn - operation. Try to replace the each by an explicite function definition ( (x)=> ) to get rid of the outer syntax sugar like so:
let
//This is the column with team Ids
Source =
Json.Document(
Web.Contents("https://api.dashpivot.com/",
[
RelativePath = "companies/projects/teams/all",
Headers=[
Authorization="JWT xxxxxxx"
]
]
)
),
Source1 = Source{0},
projects = Source1[projects],
#"Converted to Table" = Table.FromList(projects, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"teams"}, {"Column1.teams"}),
#"Expanded Column1.teams" = Table.ExpandListColumn(#"Expanded Column1", "Column1.teams"),
#"Expanded Column1.teams1" = Table.ExpandRecordColumn(#"Expanded Column1.teams", "Column1.teams", {"id"}, {"id"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1.teams1",{{"id", type text}}),
Startdate = Date.AddDays(
DateTime.Date(
DateTime.AddZone(DateTime.LocalNow(),10)),
-#"Start Days"
),
Enddate = Date.AddDays(
DateTime.Date(
DateTime.AddZone(DateTime.LocalNow(),10)),
#"End Days"
),
//Adding the column with List.Generate to iterate over the IDs
#"Added Custom" = Table.AddColumn(#"Changed Type", "Data", (x)=>
List.Generate( () =>
[
Result=
try Json.Document(
Web.Contents(
"https://api.dashpivot.com/v1/",
[
RelativePath = "teams/"&x[id]&"/photos?",
Query = [
limit="120",
offset=Number.ToText(OffsetValue),
startDate= Date.ToText(Startdate,"yyyy-MM-dd")&"T14:00:00.000Z",
endDate= Date.ToText(Enddate,"yyyy-MM-dd")&"T13:59:00.000Z"
],
Headers=[
Authorization="JWT xxxxx"
]
]
)
)otherwise null, Offset = OffsetValue],
each not List.IsEmpty([Result]),
each
[
Result=
try Json.Document(
Web.Contents(
"https://api.dashpivot.com/v1/",
[
RelativePath = "teams/"&x[id]&"/photos?",
Query = [
limit="120",
offset=Number.ToText([Offset]+120),
startDate= Date.ToText(Startdate,"yyyy-MM-dd")&"T14:00:00.000Z",
endDate= Date.ToText(Enddate,"yyyy-MM-dd")&"T13:59:00.000Z"
],
Headers=[
Authorization="JWT xxxxxx"
]
]
)
)otherwise null, Offset = [Offset]+120],
each [Result]
)
)
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 @MKE ,
unfortunately List.Generate can be a bit hard to debug. But it could be an ambiguity-problem here, as you are using it in a Table.AddColumn - operation. Try to replace the each by an explicite function definition ( (x)=> ) to get rid of the outer syntax sugar like so:
let
//This is the column with team Ids
Source =
Json.Document(
Web.Contents("https://api.dashpivot.com/",
[
RelativePath = "companies/projects/teams/all",
Headers=[
Authorization="JWT xxxxxxx"
]
]
)
),
Source1 = Source{0},
projects = Source1[projects],
#"Converted to Table" = Table.FromList(projects, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"teams"}, {"Column1.teams"}),
#"Expanded Column1.teams" = Table.ExpandListColumn(#"Expanded Column1", "Column1.teams"),
#"Expanded Column1.teams1" = Table.ExpandRecordColumn(#"Expanded Column1.teams", "Column1.teams", {"id"}, {"id"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1.teams1",{{"id", type text}}),
Startdate = Date.AddDays(
DateTime.Date(
DateTime.AddZone(DateTime.LocalNow(),10)),
-#"Start Days"
),
Enddate = Date.AddDays(
DateTime.Date(
DateTime.AddZone(DateTime.LocalNow(),10)),
#"End Days"
),
//Adding the column with List.Generate to iterate over the IDs
#"Added Custom" = Table.AddColumn(#"Changed Type", "Data", (x)=>
List.Generate( () =>
[
Result=
try Json.Document(
Web.Contents(
"https://api.dashpivot.com/v1/",
[
RelativePath = "teams/"&x[id]&"/photos?",
Query = [
limit="120",
offset=Number.ToText(OffsetValue),
startDate= Date.ToText(Startdate,"yyyy-MM-dd")&"T14:00:00.000Z",
endDate= Date.ToText(Enddate,"yyyy-MM-dd")&"T13:59:00.000Z"
],
Headers=[
Authorization="JWT xxxxx"
]
]
)
)otherwise null, Offset = OffsetValue],
each not List.IsEmpty([Result]),
each
[
Result=
try Json.Document(
Web.Contents(
"https://api.dashpivot.com/v1/",
[
RelativePath = "teams/"&x[id]&"/photos?",
Query = [
limit="120",
offset=Number.ToText([Offset]+120),
startDate= Date.ToText(Startdate,"yyyy-MM-dd")&"T14:00:00.000Z",
endDate= Date.ToText(Enddate,"yyyy-MM-dd")&"T13:59:00.000Z"
],
Headers=[
Authorization="JWT xxxxxx"
]
]
)
)otherwise null, Offset = [Offset]+120],
each [Result]
)
)
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
@ImkeF Thanks a lot. Definitely it was the ambiguity on the AddColumn. Looks like List.Generate() is one of the lazy ones. Your solution worked like magic! I appreciate
Hi @MKE ,
that's probably because your webcall returns a record and not a list. Could it be that you have to drill into the body-part instead?:
maybe: [Result][Body] ?
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 @MKE ,
what does the error-message say?
... just saw that you've used another "try" after the "otherwise". That's not correct syntax an must be deleted:
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
The try on otherwise is removed but still same error
Thanks for pointing that out @ImkeF , the error says
Expression.Error: We cannot convert a value of type Record to type List.
Details:
Value=
HasError=FALSE
Value=
Type=[Type]
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.