From either Power BI or Excel, Get Data dialogs allow you to import data from many different sources. Among the options is Web. Get Data Web automatically detects JSON formatted results from REST endpoints and starts a Power Query editor session.
From the Power Query editor session a click on the Convert To Table button starts building the M language script which will transform the JSON data package into tables suitable for driving Power BI or Excel. From there clicking on column expand icons and selecting columns continues to build the transform script.
To see the full script select the Advanced Editor.
This script will automatically be applied anytime the data is refreshed from the source.
Data relations and formats can be refined in Power BI Desktop.
Report visualization can then be assembled by simple drag and drop selection.
In order to plug Power Query directly into Manage 2000 a new JSON Service named GetData and a new PWS function named JSON.PACKAGE.MAINT have been added to release 8.1. JSON.PACKAGE.MAINT defines named selections of data. The specification for the selection is in the form a UniQuery SELECT statement with &var& replacement variables using the MSO replacement engine. So relative time frames can be defined like 'Process_Date GE "&T-7&"'.
The JSON packages can be picked up at the /MT/JSONServices endpoint using the GetData service.
So if you are looking for a way to directly plug Manage 2000 data into Power BI or Excel check out the new JSON packages. (dev patch 40126 for 8.1 sp2 and 8.1 sp3)
