Forum Discussion
Power Query - Expression.Error: Illegal characters in path, cell value as input
Hello,
I have a function GetValue(), that gets the value from a cell that I named "Query".
However when I then try to run the query, it gives me an error : Expression.Error: Illegal characters in path
This is the M code:
let
value = Text.From(Excel.CurrentWorkbook(File.Contents(GetValue("Query")))),
Source = value
in
SourceI tried adding Text.From to see if it would change anything but it didn't. I can't quite seem to understand why it's not working. The value = Odbc.Query(...), i then make source = value.
Thank you for the help
Thank you. I see only 1 row which returns Odbc.Query, but that doesn't matter. Perhaps to evaluate it you shall use something like
let a = "Excel.Workbook(File.Contents(""C:\Test\Book1.xlsx""), null, true)", Source = Expression.Evaluate(a, #shared) in Source
5 Replies
Sorry, I can't understand what do you trying to do.
- what GetValue("Query") returns, single scalar value, or list or table?
- Excel.CurrentWorkbook() works with tables and named range within current workbook, not with external file;
- standard connection to another file is = Excel.Workbook(File.Contents("filename"), null, true);
In general
- much better to split you expressions on separate steps and call them one by one;
- working with Power Query forget about cells, think in terms of tables, records and lists. For example, taking first value from the list (that could be table column) is =MyList{0}, even if the list has only one element
This is the code for GetValue()
(rangeName) => Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]In a cell or in a table, I want to have text - that is the query I want to run. The value in the lookup table/cell is a concatenation of different cells that makes the text Odbc.Query(....). The reason for the concatenations is to be able to modify the query from another table. I want to run the query: Odbc.Query(.......).
I have now tried putting the query into a table and referencing it with this fParameter function:
let Parameter=(TableName,ParameterLabel) => let Source = Excel.CurrentWorkbook(){[Name=TableName]}[Content], value = Source{[Parameter=ParameterLabel]}[Value] in value in ParameterInstead of executing the query, it loads a 1 column, two row table that looks like this:
I hope this clarifies it a little bit.
Thank you. I see only 1 row which returns Odbc.Query, but that doesn't matter. Perhaps to evaluate it you shall use something like
let a = "Excel.Workbook(File.Contents(""C:\Test\Book1.xlsx""), null, true)", Source = Expression.Evaluate(a, #shared) in Source
