Troubleshooting PowerBi Connections to ControlUp Edge DX API

A user could not connect MS Power Query or PowerBi to the Edge API and receive a list of table metadata, not the actual data. Another user suggested the query: "= Table.ExpandRecordColumn(#"Converted to Table1", "Column1", … " and shared basic Web connector settings for the user to try. The user thanked the second user for their help.

Read the entire ‘Troubleshooting PowerBi Connections to Edge API’ thread below:

Have people successfully connected PowerBI (or MS Power Query) to the Edge API?

I’m struggling – I keep getting a list of table metadata rather than the actual data.

Sorry, can’t show a screenshot, I can’t access Slack from work network 😞

But it looks something like this

…To explain the setup.

I’ve opened PowerBI, created a new data connection (Web)

Selected Advanced and entered https://TENANTNAME.sip.controlup.com/api/data/builtin_missing_patches

Added a http header item of x-api-key and added the API key.

I’ve accessed the same data via a PowerShell API session and that works fine, but I can’t get PowerBI to play nice….

Hey James, just started playing around with Power Bi. I’m by no means an expert but will see if I can look at your use case

This is the query I have in Power Bi:

= Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"rebootrequired", "version", "title", "severity", "created_local", "platform", "_created", "_created_local", "_device_id", "_device_name", "_data_source", "device_name", "_id", "kb", "cveids", "_data_source_id"}, {"Column1.rebootrequired", "Column1.version", "Column1.title", "Column1.severity", "Column1.created_local", "Column1.platform", "Column1._created", "Column1._created_local", "Column1._device_id", "Column1._device_name", "Column1._data_source", "Column1.device_name", "Column1._id", "Column1.kb", "Column1.cveids", "Column1._data_source_id"})

Which shows me these results:

Web connector settings is pretty basic:

(obviously using your own SIP URL)

@member

Thanks Joel!

Continue reading and comment on the thread ‘Troubleshooting PowerBi Connections to ControlUp Edge DX API’.  Not a member? Join Here!