PQ error

[millhouse123]

I have a power query I am trying to add a custom function to but am getting an error and I am not sure how to resolve.


My query returns the data in the following format - 2 columns

Name(workbook Name), custom.name(worksheet Name)

1001000401_2018.xlsx	, 1001000401
1001000401_2018.xlsx	, 1001000401 - Alloc OH

<tbody>
</tbody><colgroup><col><col></colgroup>

I want to apply the function below which works fine when manually entering the parameter's but I get
the following error when I run it. Thanks in advance for your time.

An error occurred in the ‘’ query. Expression.Error: We cannot convert the value "1001000401" to type Number.
Details:
Value=1001000401
Type=Type


Note that I had to use the Number.ToText function in order for PQ to recognize the input values as strings. It works perfect when manually copy and pasting the workbook and worksheet names as long as I use the Number.ToText function. I have also stripped out all the intermediate steps from this function to troubleshoot.

(filename,wsname) =>
let
    Source = Excel.Workbook(File.Contents("F:\ExpAcct\Cost Study\2018 Time Study Forms\For Nick Testing\" & filename), null, true),
    #"PlaceHolder" = Source{[Item=Number.ToText(wsname),Kind="Sheet"]}[Data]
   in
    #"PlaceHolder"

 

[MarcelBeug]

If you want to ensure that function parameters are supplied as text, then you should specify the type in the function definition, like

(filename as text, wsname as text)

Before you use your function from your table, make sure to transform your table column types to text.

Now you no longer need Number.ToText.