SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2116132%22%20slang%3D%22en-US%22%3EPower%20Query%20-%20Expression.Error%3A%20Illegal%20characters%20in%20path%2C%20cell%20value%20as%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116132%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20have%20a%20function%20GetValue()%2C%20that%20gets%20the%20value%20from%20a%20cell%20that%20I%20named%20%22Query%22.%3C%2FP%3E%3CP%3EHowever%20when%20I%20then%20try%20to%20run%20the%20query%2C%20it%20gives%20me%20an%20error%20%3A%20Expression.Error%3A%20Illegal%20characters%20in%20path%3C%2FP%3E%3CP%3EThis%20is%20the%20M%20code%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%0A%20%20%20%20value%20%3D%20Text.From(Excel.CurrentWorkbook(File.Contents(GetValue(%22Query%22))))%2C%0A%20%20%20%20Source%20%3D%20value%0Ain%0A%20%20%20%20Source%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BI%20tried%20adding%20Text.From%20to%20see%20if%20it%20would%20change%20anything%20but%20it%20didn't.%20I%20can't%20quite%20seem%20to%20understand%20why%20it's%20not%20working.%20The%20value%20%3D%20Odbc.Query(...)%2C%20i%20then%20make%20source%20%3D%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20the%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2116132%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116406%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Expression.Error%3A%20Illegal%20characters%20in%20path%2C%20cell%20value%20as%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116406%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F953798%22%20target%3D%22_blank%22%3E%40cl27274%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116378%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Expression.Error%3A%20Illegal%20characters%20in%20path%2C%20cell%20value%20as%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116378%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFantastic%20this%20works%20perfectly.%20Thank%20you%2C%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116373%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Expression.Error%3A%20Illegal%20characters%20in%20path%2C%20cell%20value%20as%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116373%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F953798%22%20target%3D%22_blank%22%3E%40cl27274%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you.%20I%20see%20only%201%20row%20which%20returns%20Odbc.Query%2C%20but%20that%20doesn't%20matter.%20Perhaps%20to%20evaluate%20it%20you%20shall%20use%20something%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20a%20%3D%20%22Excel.Workbook(File.Contents(%22%22C%3A%5CTest%5CBook1.xlsx%22%22)%2C%20null%2C%20true)%22%2C%0A%20%20%20%20Source%20%3D%20Expression.Evaluate(a%2C%20%23shared)%0Ain%0A%20%20%20%20Source%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116368%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Expression.Error%3A%20Illegal%20characters%20in%20path%2C%20cell%20value%20as%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116368%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20code%20for%20GetValue()%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E(rangeName)%20%3D%26gt%3B%20%0A%20%20%20%20Excel.CurrentWorkbook()%7B%5BName%3DrangeName%5D%7D%5BContent%5D%7B0%7D%5BColumn1%5D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3BIn%20a%20cell%20or%20in%20a%20table%2C%20I%20want%20to%20have%20text%20-%20that%20is%20the%20query%20I%20want%20to%20run.%20The%20value%20in%20the%20lookup%20table%2Fcell%20is%20a%20concatenation%20of%20different%20cells%20that%20makes%20the%20text%20Odbc.Query(....).%20The%20reason%20for%20the%20concatenations%20is%20to%20be%20able%20to%20modify%20the%20query%20from%20another%20table.%20I%20want%20to%20run%20the%20query%3A%20Odbc.Query(.......).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20now%20tried%20putting%20the%20query%20into%20a%20table%20and%20referencing%20it%20with%20this%20fParameter%20function%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Elet%20Parameter%3D(TableName%2CParameterLabel)%20%3D%26gt%3B%0A%0Alet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3DTableName%5D%7D%5BContent%5D%2C%0A%20%20%20%20value%20%3D%20Source%7B%5BParameter%3DParameterLabel%5D%7D%5BValue%5D%0Ain%0A%20%20%20%20value%0A%0Ain%20Parameter%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EInstead%20of%20executing%20the%20query%2C%20it%20loads%20a%201%20column%2C%20two%20row%20table%20that%20looks%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22cl27274_0-1612614152268.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F252916i33D6C800D8ADF038%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22cl27274_0-1612614152268.png%22%20alt%3D%22cl27274_0-1612614152268.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20hope%20this%20clarifies%20it%20a%20little%20bit.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2116322%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Expression.Error%3A%20Illegal%20characters%20in%20path%2C%20cell%20value%20as%20input%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2116322%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F953798%22%20target%3D%22_blank%22%3E%40cl27274%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20I%20can't%20understand%20what%20do%20you%20trying%20to%20do.%3C%2FP%3E%0A%3CP%3E-%20what%20GetValue(%22Query%22)%20returns%2C%20single%20scalar%20value%2C%20or%20list%20or%20table%3F%3C%2FP%3E%0A%3CP%3E-%20Excel.CurrentWorkbook()%20works%20with%20tables%20and%20named%20range%20within%20current%20workbook%2C%20not%20with%20external%20file%3B%3C%2FP%3E%0A%3CP%3E-%20standard%20connection%20to%20another%20file%20is%26nbsp%3B%3D%20Excel.Workbook(File.Contents(%22filename%22)%2C%20null%2C%20true)%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%3C%2FP%3E%0A%3CP%3E-%20much%20better%20to%20split%20you%20expressions%20on%20separate%20steps%20and%20call%20them%20one%20by%20one%3B%3C%2FP%3E%0A%3CP%3E-%20working%20with%20Power%20Query%20forget%20about%20cells%2C%20think%20in%20terms%20of%20tables%2C%20records%20and%20lists.%20For%20example%2C%20taking%20first%20value%20from%20the%20list%20(that%20could%20be%20table%20column)%20is%20%3DMyList%7B0%7D%2C%20even%20if%20the%20list%20has%20only%20one%20element%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

5 Replies

@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

@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:

cl27274_0-1612614152268.png

I hope this clarifies it a little bit.

 

best response confirmed by cl27274 (Occasional Contributor)
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

@Sergei Baklan 

Fantastic this works perfectly. Thank you, so much!