Expression.Error: We cannot convert a value of type List to type Table

[LP2803]

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

[PhilipTreacy · Accepted 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.

[PhilipTreacy · Accepted 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.

[LP2803]

@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?

[LP2803 · Accepted solution]

@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"