Feb 05 2021 04:05 PM
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
Source
I 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
Feb 06 2021 02:22 AM
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
Feb 06 2021 04:23 AM
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 Parameter
Instead of executing the query, it loads a 1 column, two row table that looks like this:
I hope this clarifies it a little bit.
Feb 06 2021 04:45 AM
SolutionThank 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
Feb 06 2021 04:58 AM
Fantastic this works perfectly. Thank you, so much!
Feb 06 2021 05:32 AM
@cl27274 , you are welcome, glad to help
Feb 06 2021 04:45 AM
SolutionThank 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