Power Query Access to Manage 2000 Data

Power Query is a common import tool used by both Power BI and Excel 2016+.

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)

Embedding Power BI Reports in Manage 2000 News Feeds

Power BI provides shareable visualizations mashed up from multiple data sources.  The data might come from Excel or SQL or ODBC.  For instance Manage 2000 data might be synced through Novo Motion Outbound to SQL server. From there Power BI might refresh reports and dashboards.

Users can consume the report through the Power BI interface.  But it is also very easy to embed Power BI reports in Manage 2000 news articles for display in the Manage 2000 function NewsReader or other RSS feed readers.

From the Power BI Service browser interface:

Select a report

Select File

Select Embed

Copy the iframe content

Paste it into a STATIC article Summary (HTML) prompt

(Recent updates to Power BI have made this a little more round about;

 After copying the iframe snippet into the Summary (HTML) prompt -

   copy the link to the app.powerbi.com website from the Secure embed code dialog to the clipboard

   use the Edit button on the right to bring the Summary (HTML) text up in the editor

   select the string inside the quotes on the right side of the "src" attribute

   paste in the app.powerbi.com url string

   save the contents back to the Summary (HTML) prompt - 8/19/2019)

(Even more recent updates corrected this hiccup.)

Copy the link to the app.powerbi.com website

Paste it into the Drill Back Document prompt
(Clicking on the news article link will send the user to the drill back URL, in this case the report in the Power BI Service. If related detail such as the original Manage 2000 data is available from another URL use that instead.)

Attach the article to a news feed and view from the Newsreader function:

Selecting a row in the airport table refocuses the map to display that specific information. The interactive features of the report remain active when viewed in a Manage 2000 newsfeed.