How to make a variable in connection string?

[mushama]

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:

https://login.onlinepos.dk/api/external/?agreementnumber=8855&username=XXX&password=YYY&function=Sal...

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

[Greg_Deckler]

Unless I am mistaken, you want to create Parameters in the Query Editor.

[mushama]

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 🙂

[v-qiuyu-msft]

Hi @mushama,

In addition to @Greg_Deckler's suggestion, you can also take a look at the sample of Scenario 1: Parameterising a Data Source in this article.

Best Regards,
Qiuyun Yu

[mushama]

Thank you - I just took a quick look at the article. It looks promising and I will have time to verify if I can use the solution during the next couple of days.

I will let you know wheter it worked or not.

Kind regards

Morten

[v-qiuyu-msft]

Hi @mushama,

Is query parameter working in your scenario?

Best Regards,
Qiuyun Yu

[mushama · Accepted solution]

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

[Greg_Deckler]

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/