Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wabanek
Frequent Visitor

Trello rest api as a data source authentication issue

Hi All, 

 

I have managed to get data from Trello rest api to my desctop report but when I published I get authentication 400 error - The credentials provided for the Web source are invalid. Not sure why and how can I fix it. Could you please support me. 

 

let
    Source = Json.Document(Web.Contents("https://api.trello.com/1/boards/cExjoJEB/cards?key=<my key>a&token=<my token>")),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "checkItemStates", "closed", "dateLastActivity", "desc", "descData", "dueReminder", "idBoard", "idList", "idMembersVoted", "idShort", "idAttachmentCover", "idLabels", "manualCoverAttachment", "name", "pos", "shortLink", "badges", "dueComplete", "due", "idChecklists", "idMembers", "labels", "shortUrl", "subscribed", "url"}, {"id", "checkItemStates", "closed", "dateLastActivity", "desc", "descData", "dueReminder", "idBoard", "idList", "idMembersVoted", "idShort", "idAttachmentCover", "idLabels", "manualCoverAttachment", "name", "pos", "shortLink", "badges", "dueComplete", "due", "idChecklists", "idMembers", "labels", "shortUrl", "subscribed", "url"}),
    #"Expanded {0}1" = Table.ExpandListColumn(#"Expanded {0}", "idMembersVoted"),
    #"Expanded {0}2" = Table.ExpandListColumn(#"Expanded {0}1", "idLabels"),
    #"Expanded {0}3" = Table.ExpandRecordColumn(#"Expanded {0}2", "badges", {"attachmentsByType", "location", "votes", "viewingMemberVoted", "subscribed", "fogbugz", "checkItems", "checkItemsChecked", "comments", "attachments", "description", "due", "dueComplete"}, {"attachmentsByType", "location", "votes", "viewingMemberVoted", "subscribed.1", "fogbugz", "checkItems", "checkItemsChecked", "comments", "attachments", "description", "due.1", "dueComplete.1"}),
    #"Expanded {0}4" = Table.ExpandListColumn(#"Expanded {0}3", "idChecklists"),
    #"Expanded {0}5" = Table.ExpandListColumn(#"Expanded {0}4", "idMembers"),
    #"Expanded {0}6" = Table.ExpandListColumn(#"Expanded {0}5", "labels"),
    #"Expanded {0}7" = Table.ExpandRecordColumn(#"Expanded {0}6", "labels", {"id", "idBoard", "name", "color"}, {"id.1", "idBoard.1", "name.1", "color"}),
    #"Expanded {0}8" = Table.ExpandRecordColumn(#"Expanded {0}7", "attachmentsByType", {"trello"}, {"trello"}),
    #"Expanded {0}9" = Table.ExpandRecordColumn(#"Expanded {0}8", "trello", {"board", "card"}, {"board", "card"}),
    #"Expanded {0}10" = Table.ExpandRecordColumn(#"Expanded {0}9", "descData", {"emoji"}, {"emoji"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded {0}10",{"checkItemStates", "closed", "desc", "emoji", "dueReminder", "idBoard", "idMembersVoted", "idAttachmentCover", "idLabels", "manualCoverAttachment", "pos", "shortLink", "board", "card", "location", "votes", "viewingMemberVoted", "subscribed.1", "fogbugz", "checkItems", "checkItemsChecked", "due.1", "dueComplete.1", "dueComplete", "idChecklists", "id.1", "idBoard.1", "name.1", "color", "shortUrl", "subscribed"})
in
    #"Removed Columns"

image.png

1 ACCEPTED SOLUTION
blopez11
Resident Rockstar
Resident Rockstar

The first parameter to Web.Contents must be static

In your case try setting it to "https://api.trello.com/", then use the relative path option to add "1/boards/cExjoJEB/cards", and query option for the paremeters "key=<my key>a&token=<my token>"

 

The below reference helped me out quite a bit

 

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

 

Good luck

View solution in original post

5 REPLIES 5
jeremyking77
Helper I
Helper I

Just to add onto what blopez11 said (so it does work)

 

Ive managed to get this working with Authentication

 

I have setup parameters in powerbi for

  • The API Key
  • The API Token
  • The board key you want to get information from

This is an example of the code block for the M code

let
Source = Json.Document(Web.Contents("https://api.trello.com/1/boards/"&TrelloBoardKey&"/cards?key="&TrelloAPIKey&"&token="&TrelloAPIToken&"")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

If you want to get Card information you can use the

 https://api.trello.com/1/boards/"&TrelloBoardKey&"/cards?

and for lists..

 https://api.trello.com/1/boards/"&TrelloBoardKey&"/lists?

and there are others.

To get your API Key go to https://trello.com/app-key
To get your API Token, you get this once you have an API Key you can get a token from the same page

trellotoken.jpg


This sums it up..  https://developers.trello.com/page/authorization

 

blopez11
Resident Rockstar
Resident Rockstar

The first parameter to Web.Contents must be static

In your case try setting it to "https://api.trello.com/", then use the relative path option to add "1/boards/cExjoJEB/cards", and query option for the paremeters "key=<my key>a&token=<my token>"

 

The below reference helped me out quite a bit

 

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

 

Good luck

Actually, after some small changes the RelativePath solved the issue. Many thanks for your support. 

Many thanks for you support. I'm trying to use your advance but I'm getting credential error - credential are incorrect. Actually it works on descope report till I refreshed the data. 

let
    Source = Json.Document(Web.Contents("https://api.trello.com",
    [
        RelativePath="/1/boards/cExjoJEB/cards",
        Query=[key="<my key>",
                token="<my token>"]
    ])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded {0}" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "checkItemStates", "closed", "dateLastActivity", "desc", "descData", "dueReminder", "idBoard", "idList", "idMembersVoted", "idShort", "idAttachmentCover", "idLabels", "manualCoverAttachment", "name", "pos", "shortLink", "badges", "dueComplete", "due", "idChecklists", "idMembers", "labels", "shortUrl", "subscribed", "url"}, {"id", "checkItemStates", "closed", "dateLastActivity", "desc", "descData", "dueReminder", "idBoard", "idList", "idMembersVoted", "idShort", "idAttachmentCover", "idLabels", "manualCoverAttachment", "name", "pos", "shortLink", "badges", "dueComplete", "due", "idChecklists", "idMembers", "labels", "shortUrl", "subscribed", "url"}),
    #"Expanded {0}1" = Table.ExpandListColumn(#"Expanded {0}", "idMembersVoted"),
    #"Expanded {0}2" = Table.ExpandListColumn(#"Expanded {0}1", "idLabels"),
    #"Expanded {0}3" = Table.ExpandRecordColumn(#"Expanded {0}2", "badges", {"attachmentsByType", "location", "votes", "viewingMemberVoted", "subscribed", "fogbugz", "checkItems", "checkItemsChecked", "comments", "attachments", "description", "due", "dueComplete"}, {"attachmentsByType", "location", "votes", "viewingMemberVoted", "subscribed.1", "fogbugz", "checkItems", "checkItemsChecked", "comments", "attachments", "description", "due.1", "dueComplete.1"}),
    #"Expanded {0}4" = Table.ExpandListColumn(#"Expanded {0}3", "idChecklists"),
    #"Expanded {0}5" = Table.ExpandListColumn(#"Expanded {0}4", "idMembers"),
    #"Expanded {0}6" = Table.ExpandListColumn(#"Expanded {0}5", "labels"),
    #"Expanded {0}7" = Table.ExpandRecordColumn(#"Expanded {0}6", "labels", {"id", "idBoard", "name", "color"}, {"id.1", "idBoard.1", "name.1", "color"}),
    #"Expanded {0}8" = Table.ExpandRecordColumn(#"Expanded {0}7", "attachmentsByType", {"trello"}, {"trello"}),
    #"Expanded {0}9" = Table.ExpandRecordColumn(#"Expanded {0}8", "trello", {"board", "card"}, {"board", "card"}),
    #"Expanded {0}10" = Table.ExpandRecordColumn(#"Expanded {0}9", "descData", {"emoji"}, {"emoji"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded {0}10",{"checkItemStates", "closed", "desc", "emoji", "dueReminder", "idBoard", "idMembersVoted", "idAttachmentCover", "idLabels", "manualCoverAttachment", "pos", "shortLink", "board", "card", "location", "votes", "viewingMemberVoted", "subscribed.1", "fogbugz", "checkItems", "checkItemsChecked", "due.1", "dueComplete.1", "dueComplete", "idChecklists", "id.1", "idBoard.1", "name.1", "color", "shortUrl", "subscribed"})
in
    #"Removed Columns"

image.png

Yes, RESTful web services are kind of a pain to work with

If you are using anonymous authentication, then the root url must support anonymous (some providers support this and others do not)

I don't know if it is an option for you, but I've used the following post to get around this

https://blog.kloud.com.au/2018/07/23/use-anonymous-authentication-on-web-data-sources-with-a-api-key...

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.