Source with parameters/queries

[Feilin]

I would like to add parameters or queries into my source when I import. I tried:

let
    Source = Excel.Workbook(File.Contents(#"Folder"&"\invoices"& #"Date"&".xls"), null, true),
[...]

where Folder and Date are parameters. This works, however, when I tried 

let
    Source = AnalysisServices.Database(
	"name",
	"name",
		[Query=SELECT [Date].[Year].&["& #"CurrentYear" &"]"
[...]

where I've tried CurrentYear as both a parameter and a query, and various combinations and orderings of ampersands, hashtags and quotation marks, alas to no avail.

Is this possible to get to work, with parameters and/or queries?

[Feilin · Accepted solution]

I found the solution!

Apparently it had to do with types. Even though I had it set to text, it didn't work, but when I set it to number and converted to text, it worked.

let
    Source = AnalysisServices.Database(
	"name",
	"name",
		[Query=SELECT [Date].[Year].&["& Number.ToText(CurrentYear) &"]"
[...]

It works with formulas and/or calculations as well, so long as it is converted to text:

let
    Source = AnalysisServices.Database(
	"name",
	"name",
		[Query=SELECT [Date].[Year].&["& Number.ToText( Date.Year( DateTime.LocalNow() - 1) &"]"
[...]

Thanks for the hints!

[v-piga-msft]

Hi @Feilin, 

---

Is this possible to get to work, with parameters and/or queries?

---

We could add parameters or queries into source in Advanced Editor.

Do you have any error when you write in Advanced Editor?

You could have view of this article and the video that may help you.

Best Regards,

Cherry

[Feilin]

@v-piga-msft, no, I didn't have any errors but it wouldn't run.

Sorry, maybe I wasn't clear enough what the problem was.