Power Query - Expression.Error: Illegal characters in path, cell value as input

[cl27274]

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

[#2]

@cl27274 

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

[#3]

@Sergei Baklan 

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.

[#4 · Accepted solution]

@cl27274 

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]

@Sergei Baklan 

Fantastic this works perfectly. Thank you, so much!

[#6]

@cl27274 , you are welcome, glad to help