Get value from other table or database in Power Query Editior

[balaji_it02]

Dear all,

I am facing problem while passing one parameter value to API URL on Advanced query editior.

Access_token is dynamic value which we are getting from SQL database everytime.

I want to pass that dynamic value to API URL for getting API Data.

When i manually assign value to variable, it working, but i want this value to picked from sql database every time this dashboard refresh.

[Jai-Rathinavel · Accepted solution]

Hi Balaji,

Refer the below code to get the parameter value dynamically by retreiving from a SQL Database. 

V_Access_Token = Sql.Database("your_server_url", "your_database", [Query="Select MAX(Column) as Value from Table;"]){0}[Value]

Please accept this as a solution if this resolves your issue.

Thanks,

Jai 🙂

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution Jai-Rathinavel  and PhilipTreacy  offered, and i want to offer some more information for user to refer to.

hello @balaji_it02 , If you have a date column in your accesstoken table, you can filter for the most recent token and apply it. e.g

let
    Source = Sql.Database(yourserver, yourdatabasename, [Query="SELECT *FROM [table] where [CreatedDate]=(select max([CreatedDate]) from [table])""]),
    AccessTokens = Source[AccessToken]{0}
in
    AccessTokens

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jai-Rathinavel · Accepted solution]

Hi Balaji,

Refer the below code to get the parameter value dynamically by retreiving from a SQL Database. 

V_Access_Token = Sql.Database("your_server_url", "your_database", [Query="Select MAX(Column) as Value from Table;"]){0}[Value]

Please accept this as a solution if this resolves your issue.

Thanks,

Jai 🙂

[PhilipTreacy]

@balaji_it02 

Create a query to get the dynamic token and then call it putting the result in V_Access_Token?

V_Access_Token = Query_to_get_Token

regards

Phil