Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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!
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!
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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |