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.
Hi there,
I have the above error mentioned in the Subject line of this thread generated from the below query.
Could anyone help me how i can resolve this.
let
Terms =
List.Numbers(1,1,100),
#"Converted to Table" = Table.FromList(Terms, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Terms"}}),
SearchSuccessful = (Term) =>
let
Source =
Json.Document(
Web.Contents(
"https://webexapis.com/v1/meetings?offset=1", [Headers=[Authorization="Bearer XXXXXXXX"]],
[Query=
[offset=Term,
max="100",
meetingType="meeting",
from="2019-11-01T00:00:00Z",
to="2020-12-31T00:00:00Z"]
]
)
),
Success = Source[success]
in
Success,
Output =
Table.AddColumn(
Terms,
"Search Successful",
each SearchSuccessful([Term])
)
in
Output
Solved! Go to Solution.
Hi @LP2803
In the Table.AddColumn step you are referring to Terms which is a List. You need to refer to #"Renamed Columns" which is the last step that results in a table.
However there were a few other minor things that needed fixing too. The List of numbers being generated only created 1 number and you were passing in a record [Term] to SearchSuccessfulthat didn't exist.
Please try this code
let
Terms = List.Numbers(1,100,1),
#"Converted to Table" = Table.FromList(Terms, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Terms"}}),
SearchSuccessful = (Term) =>
let
Source =
Json.Document( Web.Contents( "https://webexapis.com/v1/meetings?offset=1", [Headers= [Authorization="Bearer XXXXXXXX"],
Query= [offset=Text.From(Term), max="100", meetingType="meeting", from="2019-11-01T00:00:00Z", to="2020-12-31T00:00:00Z"] ]
)),
Success = Source[success]
in
Success,
Result = List.Transform(Terms, each SearchSuccessful(_)), #"Converted to Table1" = Table.FromList(Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table1"
Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
@PhilipTreacy Thanks a lot for the completed code. I really appreciate. YOU ARE THE LIFESAVER 🙂
I'm able to fetch the data with slight modification as below.
let
Terms = List.Numbers(1,10,100),
#"Converted to Table" = Table.FromList(Terms, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Terms"}}),
SearchSuccessful = (Term) =>
let
Source =
Json.Document( Web.Contents( "https://webexapis.com/v1/meetings?offset=1", [Headers= [Authorization="Bearer XXXXXXXX"],
Query= [offset=Text.From(Term), max="100", meetingType="meeting", from="2019-11-01T00:00:00Z", to="2020-12-31T00:00:00Z"] ]
)),
Success = Source[items]
in
Success,
Result = List.Transform(Terms, each SearchSuccessful(_)), #"Converted to Table1" = Table.FromList(Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table1"
Hi @LP2803
In the Table.AddColumn step you are referring to Terms which is a List. You need to refer to #"Renamed Columns" which is the last step that results in a table.
However there were a few other minor things that needed fixing too. The List of numbers being generated only created 1 number and you were passing in a record [Term] to SearchSuccessfulthat didn't exist.
Please try this code
let
Terms = List.Numbers(1,100,1),
#"Converted to Table" = Table.FromList(Terms, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Terms"}}),
SearchSuccessful = (Term) =>
let
Source =
Json.Document( Web.Contents( "https://webexapis.com/v1/meetings?offset=1", [Headers= [Authorization="Bearer XXXXXXXX"],
Query= [offset=Text.From(Term), max="100", meetingType="meeting", from="2019-11-01T00:00:00Z", to="2020-12-31T00:00:00Z"] ]
)),
Success = Source[success]
in
Success,
Result = List.Transform(Terms, each SearchSuccessful(_)), #"Converted to Table1" = Table.FromList(Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table1"
Regards
Phil
If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.
Proud to be a Super User!
@PhilipTreacy I have two more follow up question on the same if you dont mind.
1. When I try to publish this to Powerbi Workspace and schedule data refresh, I get th following error "Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again."
For this API, there is no username and password. Its just the access_token which is embeded in the query itself with "Header"
and authentication is choosen as "Ananymous". But this is not working in PowerBI Services
2. I have the refresh token to refresh the access token. Any idea how to include this in my query to refresh the access token whenever it expires?
@PhilipTreacy Thanks a lot for the completed code. I really appreciate. YOU ARE THE LIFESAVER 🙂
I'm able to fetch the data with slight modification as below.
let
Terms = List.Numbers(1,10,100),
#"Converted to Table" = Table.FromList(Terms, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Terms"}}),
SearchSuccessful = (Term) =>
let
Source =
Json.Document( Web.Contents( "https://webexapis.com/v1/meetings?offset=1", [Headers= [Authorization="Bearer XXXXXXXX"],
Query= [offset=Text.From(Term), max="100", meetingType="meeting", from="2019-11-01T00:00:00Z", to="2020-12-31T00:00:00Z"] ]
)),
Success = Source[items]
in
Success,
Result = List.Transform(Terms, each SearchSuccessful(_)), #"Converted to Table1" = Table.FromList(Result, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table1"
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.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |