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

[Anonymous]

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,

[artemus]

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.

[DavidRichy]

Thank you for your second response. I've changed data privacy option and it's work now

[Anonymous]

Hi @artemus,

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

Regards, 

[Anonymous]

Hi @Anonymous,

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

[Anonymous]

Hi @Anonymous ,

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. 

Regards,

[ImkeF]

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.

[Anonymous]

hI @Anonymous 

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

[Anonymous · Accepted solution]

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,