cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mushama Frequent Visitor
Frequent Visitor

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:

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
mushama Frequent Visitor
Frequent Visitor

Re: How to make a variable in connection string?

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

View solution in original post

7 REPLIES 7
Super User
Super User

Re: How to make a variable in connection string?

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

mushama Frequent Visitor
Frequent Visitor

Re: How to make a variable in connection string?

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 🙂

Highlighted
Super User
Super User

Re: How to make a variable in connection string?

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/

 


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Moderator v-qiuyu-msft
Moderator

Re: How to make a variable in connection string?

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mushama Frequent Visitor
Frequent Visitor

Re: How to make a variable in connection string?

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

Moderator v-qiuyu-msft
Moderator

Re: How to make a variable in connection string?

Hi @mushama,

 

Is query parameter working in your scenario?

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mushama Frequent Visitor
Frequent Visitor

Re: How to make a variable in connection string?

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

View solution in original post

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (3,030)