End user in Pakistan industry love the Excel. I worked in local Pepsi franchise for two years. Everything they need raw data or reporting data into excel. They called it cycle. Because their office work cycle starts and end on excel. How much complex report you developed for them, if it not converted into excel it is useless (I was working in Oracle 6i reports at that time). So recently I decided to explore excel power query that helps to extract complex data from AX to excel. Later user can easily convert or format according to his / her needs. So here are some my key notes to connectivity with Excel power Query.
Currently for this article I used demo data and Microsoft demo vm AX R3 cu9.
For Excel power Query you have to download add in from following link. It works fine for excel 2013.
When you open the above link in browser following screen will open
Click on download button. Following screen will open
As per my machine has 64 bit version of excel so I download 64 bit version of it.
After download run it setup.
Click on next
Click on next
Finally click on finished button.
Now open Excel and you find following new tab will be added on tab strip.
If you did not find this tab in excel. Check the excel option and enable power query add-in. You can possibly found it at this location.
Now its time to connect excel with Ax. For this you have to verify that you AIF services are successfully configured and deployed on AX. If not then generate incremental CIL to verify that no error in your environment and AIF services are successfully working.
Now open Ax client and go in organization administrator module. You can follow menu link as
Organization administrator =>Setup=> Document management=> Document data source
On click on it you found following form.
As I am going to test on out of the box Query so I decided to use “USSalesUSMF2011”. You want to use your own query then select it in above screen and also activate it.
Now switch back to excel and from Power Query tab select “From Other Sources” => “From OData Feed”
From new screen select following Ax Query service with default url
http://localhost:8101/DynamicsAX/services/OdataQueryService
Query address will be vary if you are using excel add in on client and AOS on some other location.
Click on Ok. From next screen click on windows screen. As I am trying to connect on demo machine with default admin user so I select my current credentials. Otherwise you have to create a user in AX and he has to write Access on ODataQueryService and all tables in side Query to fetch data.
Click on next you find navigator from
Select required query. And click on ok.
You find data against query in Excel as following
Now if you expert in excel, you can make magic from here.