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

[LP2803]

Hi all,

I have the above error while trying to fetch the data from API with the below code. 

Any idea what could be possibly going wrong with my code?

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 XXXXXXXXXXXX"],
                                        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),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table1", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"id"}, {"Column1.id"}),
    #"Column1 id1" = #"Expanded Column2"[Column1.id],


Terms1 = Table.FromList( #"Column1 id1", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns1" = Table.RenameColumns(Terms1,{{"Column1", "Terms1"}}),

SearchSuccessfuls = (Term1) =>
                        let
                        Source1 =
                        Json.Document( Web.Contents( "http://analytics.webexapis.com/v1/meeting/qualities?meetingId=721ABD71A468968FE053D406FC0A17B4_I_177599541797726753", [Headers= [Authorization="Bearer  XXXXXXXXXXXX"],
                                        Query= [meetingId=Text.From(Term1), max="100"]])),
                        Success1 = Source1[items]

                        in
                        Success1,
Result1 = List.Transform(Terms1, each SearchSuccessfuls(_)), 
#"Converted to Table11" = Table.FromList(Result1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column11" = Table.ExpandRecordColumn(#"Converted to Table11", "Column1", {"meetingInstanceId", "webexUserName", "webexUserEmail", "joinTime", "leaveTime", "clientType", "clientVersion", "osType", "osVersion", "hardwareType", "speakerName", "networkType", "localIP", "publicIP", "cameraName", "microphoneName", "serverRegion", "participantId", "participantSessionId"}, {"Column1.meetingInstanceId", "Column1.webexUserName", "Column1.webexUserEmail", "Column1.joinTime", "Column1.leaveTime", "Column1.clientType", "Column1.clientVersion", "Column1.osType", "Column1.osVersion", "Column1.hardwareType", "Column1.speakerName", "Column1.networkType", "Column1.localIP", "Column1.publicIP", "Column1.cameraName", "Column1.microphoneName", "Column1.serverRegion", "Column1.participantId", "Column1.participantSessionId"})


in
    #"Expanded Column11"

[mahoneypat · Accepted solution]

You can just use Terms1[id] instead (case sensitive on table name) to get the list form of that column from the table variable.

Regards,

Pat

[PhilipTreacy · Accepted solution]

Hi @LP2803 

With these 2 lines

Terms1 = Table.FromList( #"Column1 id1", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns1" = Table.RenameColumns(Terms1,{{"Column1", "Terms1"}}),

You've created a table called Terms1 and then renamed the Column1 in the that table to Terms1.  So you should have something like this (I've made up the ID values of course)

If you want to pass that column of ID's into the function and get the related web info then use this

Result1 = List.Transform(#"Renamed Columns1"[Terms1], each SearchSuccessfuls(_)), 

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]

@mahoneypat @PhilipTreacy 

Thanks a lot. Both of your solutions worked for me. 

Once i get this list of Terms1 i have "Error"s in the list. Any idea how can i filter out these Error and keep only the ones resulting in List?

[LP2803]

I removed the Errors by adding this step after Result1

RemovedErrors = List.Select(
List.Transform(List.Positions(Result1), each try Result1{_} otherwise null),
each _ <> null)

[mahoneypat · Accepted solution]

You can just use Terms1[id] instead (case sensitive on table name) to get the list form of that column from the table variable.

Regards,

Pat

[mahoneypat]

Look at your Results1 step.  Terms1 appears to be a table used in a List.Transform

Result1 = List.Transform(Terms1, each SearchSuccessfuls(_)),

Regards,

Pat

[LP2803]

@PhilipTreacy & @mahoneypat 

Yes. I have this error in step "Result1"

How do i resolve this?

Im trying to get the "Column1 id1"  in a list and then pass it to the Source1 to get the relevant information for that particular id from Web.

Kindly help.

Also, please suggest me some good tutorial for M query as I have decided to learn since its really taking my sleep away everynight 🙂

[PhilipTreacy]

Hi @LP2803 

You'll have to check the steps to see which one(s) is/are causing the problem.

It'll be something where you are explicitly converting something to a List or the function requires a list as a parameter.  Like 

Table.FromList(Result1, Splitter.SplitByNothing(), null, null, ExtraValues.Error)

or

List.Transform(Terms1, each SearchSuccessfuls(_)),

Phil

[mahoneypat]

Which step is giving the error?

Pat