Forum Discussion
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,
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
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,
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.
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
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,
