PowerBI

I am trying to import my YNAB data into PowerBI using the API. Could someone post detailed instructions for how to import my data into PowerBI?

6replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Tomato Wildcat ! The API was developed primarily for people who are familiar with API setups and coding, but we can definitely see what we can do to help.

    The best place to start is our API Starter Kit, which may give you some resources. 

     

    On the side of PowerBI, it looks like this module should contain the information you need. 

     

    Let us know what you build! 

    Like
  • You will need to generate an access token in the developer settings in your YNAB account (follow the instructions on the YNAB API page on how to do that).

    Hopefully you are familiar with PowerBI. I used the good old trial and error method along with this handy YNAB API endpoint guide.

    Use the get data "web" connector and switched to advanced.  Enter the API URL in the URL parts. In the HTTP request header dropdown select "Authorization" and put "Bearer <token key>" (without quotes).  

    I haven't gotten too much further than importing all my transactions and plotting them, but I think the next steps would be to import the different classes (e.g. accounts, transactions, categories) and use the modelling tool (aka relationship builder) to map the unique classes to each other). 

    Here's the resulting powerquery syntax to get the transactions of the last used budget. You'll need to do some navigating afterward to get the data out into a table form.

    Json.Document(Web.Contents("https://api.youneedabudget.com/" & "v1/budgets/" & "last-used/transactions", [Headers=[Accept="application/json", Authorization="Bearer <token key>"]]))

    PS: Divide the "amount" column by 1000 if you're in USD. 

    Like
  • I haven't been able to successfully link to YNAB data using the process you described Steel Blue Chef , Did you have errors with authentication?

    Like
    • Beige Cartridge I've been using this method reliably for the past few months. What step are you stuck at? 

      1. Getting an API token
      2. Verify token
      3. Powerquery syntax
      4. Power BI specific issues (firewall, etc)

      On the interactive API endpoint page, click the green authorize at the top right, and paste your token and exit the window. Click one of the get buttons on the page, the "Try it out" button, and then finally execute. After a brief loading it will display the curl command, and the server response body.  You can copy the curl command and paste into command prompt (on a recent version of windows 10) or terminal on Mac or Linux. Hopefully you see the same response as shown in the browser.

      In my powerquery sample, be sure to replace the string <token key> with your token key 64 characters 0-9 and a through f.

      Like
    • Steel Blue Chef I'm having similar problems. When I follow your advice...

      Steel Blue Chef said:
      Use the get data "web" connector and switched to advanced.  Enter the API URL in the URL parts. In the HTTP request header dropdown select "Authorization" and put "Bearer <token key>" (without quotes).  

      ...1 get errors about authentication. I generated an API token, verified it in the interactive console and when connecting to PBI via "Get Data" as you described above, It's not letting me in.

      I'm relatively new to PBI, so it could be that I'm missing a step. Any help would be appreciated.

      Thanks!

      Like
    • Beige Cartridge Or make a new blank query, switch to the advanced editor in the view tab and paste the template. Then enter your token in the token parameter variable.

       

      let
          token = "pasteyourapitokenhere",
          ynab = Web.Contents("https://api.youneedabudget.com/v1/budgets/" & "last-used/transactions", [Headers=[Accept="application/json", Authorization="Bearer " & token]]),
          Source = Json.Document(ynab)
      in
          Source

      Then click the yellow bar "Edit Credentials" and select anonymous (because they were hard coded). Obviously, beware of pasting your code anywhere since it has your key hardcoded. 

      Like
Like Follow
  • 1 mth agoLast active
  • 6Replies
  • 330Views
  • 6 Following