Forum Discussion
How to make a variable in connection string?
Hi all,
I've been using PowerBI for some time now and are familiar with building reports based on SQL data - for historic purpose.
Now, I have some restaurants and would like to get data for the past 7 days automatically each 15 minutes. The data source is a web service and the connection string looks like this:
The remote system is using epoch time, thus this syntax: from=1470009601&to=1472515199
Somehow I need PowerBI to replace "from" with now and 7 days back and "to" with now (as in this moment)
Is that somehow possible?
If it's not possible because PowerBI doesn't allow scheduled Get Data how do I put up the possibility to manually choose a date and have it converted into a variable in the connection string?
Any help is appreciated, thanks.
Kind regards,
Morten Schaumann
Hi,
I don't thnik I am skilled enough to find the right solution. I might decide to just use Power BI for historical data. Data I will manually upload once a week. It only takes a few minutes each time.
I already have a real-time solution running at Klipfolio that works great. I will just add the klipfolio dashboard in an iFrame on my Sharepoint online portal.
Thanks for asking and thanks all for your input.
Kind regards
Morten
7 Replies
Unless I am mistaken, you want to create Parameters in the Query Editor.
Maybe you can tell me - I have absolutely no idea of what is the right way to do it.
So, if you have any good suggestion of a syntax you would be hero of the day if sharing it with me :-)
Sure, in this example, I have a parameter called "File". I have two options set for this parameter, "datasource1.csv" and "datasource2.csv". The "M" code looks like this:
let Source = Csv.Document(File.Contents("c:\temp\powerbi\" & File),[Delimiter=",", Columns=2, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Category", type text}, {"Value", Int64.Type}}) in #"Changed Type"You can learn more about parameters here:
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/
