Forum Discussion
Source with parameters/queries
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?
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!
3 Replies
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.
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!
