Expression.Error: We cannot convert the value "[Record]" to type List. Details: Value=[Record]

[NatDub]

Hi,

I am simply merging 2 tables, but I am getting the following error all of a sudden after the query used to work well:

Expression.Error: We cannot convert the value "[Record]" to type List.
Details:
Value=[Record]
Type=[Type]

I get the 2 tables in different formats (xlsx and csv) from a SharePoint site location. Every month, new datasets get added and they get combined to the old ones. The new data sources have not changed in format, but if so, I have anyway tested the query with old datasets when the query used to work.

I have also tested using files stored directly on my machine, in case it had to do with the source, but it gives me an error too.

The only way it has worked is with a sort of manual smaller version of directly importing the files. I am trying to rebuild the query step by step to identify the issue but when I rebuild the combination of files with a function, it starts failing again, eventhough I am only feeding the 1 file I tested it before when loading it directly. I can't understand why it's not working all of a sudden. At least the error shown is consistent.

Please help!

[Anonymous]

Hi @NatDub ,

1. Please check the query steps in the Advanced Editor. Sometimes, steps that Power BI manually adds or automatically generates during a merge operation can introduce problems. Open the Advanced Editor from the Home tab of the Query Editor and look carefully at the M-code for any steps that may be causing conversion errors. Look for any attempts to convert or interpret records as lists. Here are some links that are similar to your problem and hopefully will help you.Solved: Expression.Error: We cannot convert a value of typ... - Power Platform Community (microsoft...., Solved: Error: convert the value to type List. - Microsoft Fabric Community, powerquery - power query Expression.Error: We cannot convert a value of type Function to type List i....

2. Try using the Table.TransformColumns function, which is a Power Query M function that transforms the table by applying each of the column operations listed in transformOperations, with optional defaultTransformation and missingField parameters. The function returns a table with the specified column transformations applied. For more information, see the documentation: Table.TransformColumns - PowerQuery M | Microsoft Learn.

3. If you still can not solve your problem, I hope you can provide more detailed information, such as relevant screenshots, M code, etc., in order to better help you solve the problem.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NatDub · Accepted solution]

Hi v-laiyue-msft,

Thank you very much for taking the time to reply to me with those options.

I went onto giving a last try at testing sourcing the data in different ways, as the table right before the merge didn't have any differences regardless of where I sourced it from and the M-code didn't have any weird configurations then. At some point in my tests this error below appeared too:

Underlying error message: We cannot convert the value "[Binary]" to type Binary

So this made me believe that it coudn't be to do with the transformations, M-code or the data sources as such. So searching again I found this thread, which guided me to the resolving the problem:

https://community.fabric.microsoft.com/t5/Service/Underlying-error-message-We-cannot-convert-the-val...

This posts suggests the issue it's to do with the "Privacy level" setting. Eventhough, mine were all the same, unlike theirs, I changed them all from Organisational to Private and it worked. 🙂

I still don't understand the logic of this but am really happy is now working at last. Thanks again for your all your guidance and help.

[souza001]

For me the issue was also solved through data privacy settings/classification but private hadn´t worked for me.

What solved:

On PQuery Data Sources I´ve cleared all permissions and connected again.

Then I classified the sources as "none".

After that, on the table, I selected none and "ignore all privacy levels"

Note: before the solution above, when I´d merged 2 tables "as new" it worked but I didn´t want to create a new table to solve the situation so the privacy options changes above were the actual solution