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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
THEG72
Helper V
Helper V

API connector and POWER BI integration

Hi Power BI users,

 

Does anyone know, about how the new version of MYOB Accountright 2016.3 (SQL) can hook up to power bi.???

 

The older version Accountright Premier/Enterprise uses a 32 bit ODBC....unfortunately it doesnt connect to Power bi only via excel.

 

The new version uses a desktop web connector for cloud conenction and the server version has API but i cant see how to work the solution into Power BI?

 

I have seen a video demo of it ....?

https://www.youtube.com/watch?v=FzSBf5n1KBo

 

Any ideas? Cheers again....

10 REPLIES 10
v-yuezhe-msft
Employee
Employee

Hi @THEG72,

Please review the following blogs about consuming MYOB data with MYOB AccountingRight API via Web connector. In Power BI Desktop,  you should enter URL like: http://[IP ADDRESS OF COMPUTER TO ACCESS]:8080/accountright/GUID/XXXX/XXXX/?api-version=v2&format=json.

Getting Started
Using MS Excel to connect

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft

 

Thanks for the information but that information is very limited and not really understandable in how you get from those steps into Power BI....

 

For example,  

 

1. http://localhost:8080/accountright/ (It displays the myob data files and prompts for MYOB credentials login..so far ok)

2. It then Lists AbsoluteUri which are links to tables like Account, Contact, GeneralJournal endpoints..

3. I click on Account URI and get alist of accounts

Accounts.PNG

 

Per info, I can use the version 2 endpoints by adding as follows;

http://localhost:8080/AccountRight/91a5c87e-884f-42f5-b4d5-0f924164e116/Account/?api-version=v2

 

 

Ok endpoint is retrieved and i go to open Excel..and get web data...

 

I paste the end point in and can add a formatting command...&format=html 

http://localhost:8080/AccountRight/91a5c87e-884f-42f5-b4d5-0f924164e116/Account/?api-version=v2&format=html

It reconfirms Login crenditials and then displays data

Excel.PNG

 you can also use &format=json..too i think

 

 

 i click import and the base of the screen shot...and then nothing happens computer just crashes in Excel....

 

MYOB states "This is just step one in a set of tutorials focused on MS Excel and our MYOB AccountRight Live API, come back soon for more exploratory MS Excel Tutorials"

 

This hasnt been updated for a long time so surely there is an easier way.....

 

Hopefully someone in know can assist!!

 

 

 

 

 

Is Power BI not setup to connect to this data source easily like other packages ? with the old version (using 32 odbc) you can connect tables to Excel, build a model which Power BI connects ti easily...

Hi @THEG72,

What is the result when you enter http://localhost:8080/AccountRight/91a5c87e-884f-42f5-b4d5-0f924164e116/Account/?api-version=v2&format=json in "Get Data->Web" entry in Power BI Desktop? 
1.PNG

In Power BI Desktop, there is no built-in connector for MYOB accounting, you can submit an idea in this forum about this issue.

Moreover, as far as I know, the new 20xx versions of MYOB Accounting no longer have ODBC, you are not able to create DSN for MYOB Accounting and connect to the ODBC data source in Power BI Desktop.

Regards,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

HI @v-yuezhe-msft

 

Yes the new version uses an SQL database and has an API to connect to..but i havent seen any instructions on how this works..

 

The older version uses a 32bit ODBC driver which i used to connect in Excel and extract tables...Power BI doesnt do 32bit ODBC does it?

 

WHen i enter the connection into Power bi i get the following:

Capture1.PNG

I am not sure how i can retreieve the items in the List...as its not listing the tables as such...But i can see records

Capture2.PNG

 

Is there a different method to access the files stored as json format?

@THEG72,

In your scenario, you are not able to use ODBC entry in Power BI Desktop to connect to your data source as you are unable to create DSN for your MYOB Accounting. As far as I know, you can only use the API to get data from MYOB in Power BI Desktop.

Regarding to your JSON source in Power BI Desktop, please follow the instructions in the following similar thread and blog to get the data.

https://community.powerbi.com/t5/Desktop/Stuck-with-quot-nested-quot-records/td-p/65865

https://blogs.msdn.microsoft.com/lukaszp/2016/04/17/working-with-json-data-in-power-bi-desktops-quer...

Regards,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

HI @v-yuezhe-msft

Thanks for your time in replying to my request.

 

You are right the API is for the new version of MYOB AccountRight 20xx...I will examine your reference to how to access the data from the json export...

 

With the older version of MYOB (Premier), I am able to create a DSN connetion to my MYOB Accounting Premier File as shown in the link below:

 

http://help.myob.com/wiki/display/supar/Creating+a+Data+Source+Name+%28DSN%29+and+test+connection+in...

 

Its a 32 bit MYOB ODBC driver the DSN is using for the connection...So i belive the issue is Power BI doesnt accept a 32 bit ODBC connection? Is that correct? it can only do 64 bit?

 

 

 

 

 

 

@THEG72,

32 bit Power BI Desktop accepts 32 bit ODBC connection. You would need to contact MYOB support about that why they don't provide ODBC driver for MYOB AccountRight 20xx.


Regards,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yuezhe-msft

 

I tried the 32 bit version with odbc driver from Myob but i get an error but it does seem to find all the tables.

 

This my dsn

CaptureODBC.PNG

 

i am using 32bit version of Power BI....

Capturepbi32.PNG

on loading the account table i get error

 

1.PNG

2.PNG

I click load and get error below, :unable to read beyond the end of stream.."

 

Captureperror.PNG

heres theoutput log

https://1drv.ms/t/s!AnS2TMz05ePlgQXCISetoGDBXy0M

 

@THEG72,

Do you create DSN for MYOB Accountright 2016.3 using old version of ODBC driver?

Regards,

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

No, this is for the Accounting Premier older version as the ODBC 32bit works fine on this. But i get the error shown previously when trying to connect..

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.