Auto refresh schedule not working in Power BI

monday Apps & Developers
, ,
1

Hello All,
I am using this small code in M query (power BI) to extract data out of monday board.
But when i try to schedule a refresh in power BI platform it says “not authenticated”.
Can any one help me to find a way to add authentication in the code or how can i login to this
https://api.monday.com/v2 so that power BI can authenticate itself. Any help will be appreciated.

here is the code.

let
Key = “api token”,
Board = “board id”,
Source = Web.Contents(
“https://api.monday.com/v2”,
[
Headers=[
#“Method”=“POST”,
#“Content-Type”=“application/json”,
#“Authorization”=“Bearer " & Key
],
Content=Text.ToBinary(”{““query””: ““query { boards(ids: " & Board & “) { items { name, columns: column_values { title, text } } } }””}”)
]
),
//Line 19 converts the data inside “data → board-> items” to a table with the column names “Title”, “Updated At”, “Group”, “Columns”
Data = Table.FromList(Json.Document(Source)[data][boards]{0}[items], Record.FieldValues, {“Title”, “Columns”}),
#“Expanded Columns” = Table.ExpandListColumn(Data, “Columns”),
#“Expanded Columns1” = Table.ExpandRecordColumn(#“Expanded Columns”, “Columns”, {“title”, “text”}, {“Columns.title”, “Columns.text”}),
#“Pivoted Column” = Table.Pivot(#“Expanded Columns1”, List.Distinct(#“Expanded Columns1”[Columns.title]), “Columns.title”, “Columns.text”)
in
#“Pivoted Column”

2

Hey @Deepankar,

could you try:

Key = "Bearer api_token"

 #"Authorization"= Key

Greetings

3

Hello @TMNXT-Dev,
Thanks for your response. Can you please let me know where can I find The “bearer api token“?

4

Ah my bad.

Just type Bearer and then add your token.
Kind of like this:

Key = "Bearer dsawe21345322....."

5

Hey hey @Deepankar :wave:

Thanks for reaching out to the community and allowing us to help! My warmest welcome and I hope you enoy your stay with us. :star:

@TMNXT-Dev thanks so much for your quick and helpful suggestion as usual! I do think that should be the solution to the issue.

Just to drive this point further, you can find your API v2 key in your monday.com account’s Admin section (if you are an Admin). You’ll need to jump to the API tab, like so:

image
image1448×620 78.1 KB

Let me know if this helps :wink:

-Alex

6

Hello @TMNXT-Dev
I tried the way you mentioned. But i still couldn’t authenticate in Power BI for schedule refresh.
Authentication method is basic. Error is:
Failed to update data source credentials: The credentials provided for the Web source are invalid. (Source at https://api.monday.com/v2.)

Even if Authentication level is anonymous, it’s still the same error.

Also in the Gateway option i cannot see my personal gateway and see something like
“You don’t need a gateway for this dataset, because all of its data sources are in the cloud, but you can use a gateway for enhanced control over how you connect.”

Can you suggest me something in this case.

Hi @AlexSavchuk
Thanks for your warm regards.

7

Hey @Deepankar,

could you share the whole code again please ?

Greetings

8

let
Key = “Bearer eyJhbBciPiJIAzI1NiJ9.ey…”,
Board = “123456789”,
Source = Web.Contents(
“https://api.monday.com/v2”,
[
Headers=[
#“Method”=“POST”,
#“Content-Type”=“application/json”,
#“Authorization”= Key
],
Content=Text.ToBinary(“{”“query”": ““query { boards(ids: " & Board & “) { items { name, columns: column_values { title, text } } } }””}”)
]
),
//Line 19 converts the data inside “data → board-> items” to a table with the column names “Title”, “Updated At”, “Group”, “Columns”
Data = Table.FromList(Json.Document(Source)[data][boards]{0}[items], Record.FieldValues, {“Title”, “Columns”}),
#“Expanded Columns” = Table.ExpandListColumn(Data, “Columns”),
#“Expanded Columns1” = Table.ExpandRecordColumn(#“Expanded Columns”, “Columns”, {“title”, “text”}, {“Columns.title”, “Columns.text”}),
#“Pivoted Column” = Table.Pivot(#“Expanded Columns1”, List.Distinct(#“Expanded Columns1”[Columns.title]), “Columns.title”, “Columns.text”),
#“Changed Type” = Table.TransformColumnTypes(#“Pivoted Column”,{{“EG [m²]”, type number}})
in
#“Changed Type”

9

There actually issues with cloud and http request in Power BI.

There is a whole article about this here:

Tell me if that helped.

Greetings

10

@TMNXT-Dev,
I get to know this as well. And already went through the link you provided. But unfortunately of no help.

11

To eliminate my last doubt could you enter your key directly into the Authorization header please.

#“Authorization”= "Bearer eyJhbBciPiJIAzI1NiJ9.ey… "

If that doesn’t work, I think it’s the problem with the skip test http request within power BI.

12

Hi @TMNXT-Dev,
It didn’t worked unfortunately.
is it http request within Power BI? Or it is because of the Monday API ?
because i found some information where it is possible with different web sources.

14

Hey @Deepankar,

Are you receiving the authentication error from PowerBI? If you try the API key on our try it yourself page, do you also receive an error?

-Daniel

15

Hi @dsilva,
Authentication is working in try it yourself
using my API key.

16

Hey @Deepankar - thank you for confirming this.

To be completely honest here I’m not quite sure at the moment where the problem is occurring (whether it is on the on the PowerBI side, or a response from the API due to a malformed auth header).

I’d like to get a better picture of what you’re seeing on your end - would you mind posting some screenshots walking us through the steps you’re taking on your end and showing the error you’re seeing? This will help us better understand.

-Daniel

17

@dsilva @AlexSavchuk @TMNXT-Dev @dipro
Thank you all for your responses. It worked finally. Actually we don’t have to authenticate the credentials in power BI as we are using the key in script. we have to check “skip test” option while providing credentials and that’s all.

4 Likes
18

Hey @Deepankar - awesome!! So glad that worked, thank you for sharing.

Hopefully this helps if anyone else in the community is running into any issues.

-Daniel

1 Like