cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Mariedegr Frequent Visitor
Frequent Visitor

Expression.Error: Cannot convert a value of type Table to type Table

Hi,

 

Here's my case. Thanks for any help on this. 

 

I was first combining Excel files in a query. Then, I wanted to merge this query with a query coming from an OData feed. Here I got a Formula.Firewall error message which I resolved with the following article : https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

 

After staging my queries, I merged them again, but now I got the following message : Expression.Error Cannot convert a value of Type Table to Type Table. 

 

What I'm confused about is that both my queries are table before merging. What exactly is going wrong and how can I fix this ?

 

Regards,

1 ACCEPTED SOLUTION

Accepted Solutions
Mariedegr Frequent Visitor
Frequent Visitor

Re: Expression.Error: Cannot convert a value of type Table to type Table

Hi,

 

I found out what was going on. It was simple in fact.

 

Both column I was trying to merge were not the same data type so I changed both to Text.

 

Regards,

View solution in original post

7 REPLIES 7
jborro Member
Member

Re: Expression.Error: Cannot convert a value of type Table to type Table

Hi @Mariedegr,

 

I suspect that at some point in time you are passing a wrong parameter to a function, but it is virtually impossible to guess without seeing at least your code. Can you please post it (without any private/sensitive data). Also would be great if you could locate it to a step where the error appears the first time - just click back through your query step-by-step and see when the error disappears. Please note, that usually if the PBI does not suggest to go straight to the error it may be in one of the source queries/tables.

 

Kind regards

JB

artemus Member
Member

Re: Expression.Error: Cannot convert a value of type Table to type Table

Are you using Analysis services? If so, it has some functions that only work on a cube, which is a specialized table. If you staged your data to a differnt datasource, then this additional functionality is not avialable.

 

Also, if you are not concerned with the possiblity of leaking privacy data from one data source to another, you can turn off the privacy checks in the options, which I think will remove this error.

Mariedegr Frequent Visitor
Frequent Visitor

Re: Expression.Error: Cannot convert a value of type Table to type Table

Hi @jborro ,

 

Hope what follows is clear. There's a bit of French through the code, sorry for that. 

 

1. Here's my code for combining my Excel files :

 

 

(filepath)=>
let
    Source = Excel.Workbook(File.Contents(filepath)),
    SHEET1_Sheet = Source{[Item="SHEET1",Kind="Sheet"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(SHEET1_Sheet),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"SALES_DOCUMENT", Int64.Type}, {"WEEK_ENDING", Int64.Type}, {"REFERENCE", type text}, {"CDM_DOCUMENT", Int64.Type}, {"CATEGORY_MANAGER_NUMBER", Int64.Type}, {"MERCHANDISE_CATEGORY", type text}, {"MC_DESC", type text}, {"PRICE_FAMILY", type text}, {"PF_MASTER_ARTICLE", Int64.Type}, {"PF_GROUP", type text}, {"ARTICLE", Int64.Type}, {"ARTICLE_DESCRIPTION", type text}, {"EAN_UPC", Int64.Type}, {"UOM", type text}, {"CASE_PACK", Int64.Type}, {"RETAIL_SIZE", type text}, {"Percentage Amount", type text}, {"Calculation Basis", type text}, {"QUANTITY", Int64.Type}, {"PRICE", type number}, {"AMOUNT", type number}, {"BANNER", type text}}),
    #"Valeur remplacée" = Table.ReplaceValue(#"Type modifié",".",",",Replacer.ReplaceText,{"Percentage Amount", "Calculation Basis"}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Valeur remplacée",{{"Percentage Amount", type number}, {"Calculation Basis", type number}, {"AMOUNT", type number}, {"PRICE", type number}, {"QUANTITY", type number}})
in
    #"Type modifié1"

 

 

2. Here's my code for my query before staging :

 

 

let
    Source = Folder.Files("..."),
    #"Personnalisée ajoutée" = Table.AddColumn(Source, "Custom", each function([Folder Path]&[Name]))
in
    #"Personnalisée ajoutée"

 

 

3. Here's my code for staging query and in which I merged with the Odata feed Cross Reference :

 

 

let
    Source = Requête2,
    #"Custom développé" = Table.ExpandTableColumn(Source, "Custom", {"SALES_DOCUMENT", "WEEK_ENDING", "ARTICLE", "ARTICLE_DESCRIPTION"}, {"SALES_DOCUMENT", "WEEK_ENDING", "ARTICLE", "ARTICLE_DESCRIPTION"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Custom développé",{"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonnes supprimées",{{"SALES_DOCUMENT", Int64.Type}, {"WEEK_ENDING", Int64.Type}, {"ARTICLE", Int64.Type}, {"ARTICLE_DESCRIPTION", type text}}),
    #"Requêtes fusionnées" = Table.NestedJoin(#"Type modifié",{"ARTICLE"},CrossReference,{"Cross_Reference_No"},"CrossReference",JoinKind.LeftOuter)
in
    #"Requêtes fusionnées"

 

 

 

In print screen you can see at which step I get Expression.Error. It's litteraly at the last step when merging. 

Expression.Error.PNG

 

Regards,

Mariedegr Frequent Visitor
Frequent Visitor

Re: Expression.Error: Cannot convert a value of type Table to type Table

Hi @artemus,

 

I'm not using Analysis Services and for now turning off privacy settings is the last option I want to consider.

 

Regards, 

 

 

ImkeF
MVP

Re: Expression.Error: Cannot convert a value of type Table to type Table

My guess is that the error sits somewhere else in the references queries (either in Requête2 or CrossReference).

Some errors in columns just "surface" when being part of merges like here.

The error-message indicates that you tried to reference a column in squared brackets but wrapped that into quotes, so it became a text instead of a column reference.

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




jborro Member
Member

Re: Expression.Error: Cannot convert a value of type Table to type Table

hI @Mariedegr 

Agree with @ImkeF , usually if an error relates to the current query you would see a button "Go To Error".

Please check the source tables' (Requête2 or CrossReference) output.

 

Kind regards,

JB

Mariedegr Frequent Visitor
Frequent Visitor

Re: Expression.Error: Cannot convert a value of type Table to type Table

Hi,

 

I found out what was going on. It was simple in fact.

 

Both column I was trying to merge were not the same data type so I changed both to Text.

 

Regards,

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,345)