eWON Flexy with Power BI

[Franck]

Hi Tim,

I solve the problem by reading your documents (Reference Guide For DMWeb) . Everything is explained about the “transactionId” parameter and how to use it.

Now I’m strunggling with Power Bi. I want to use PowerQuery. I set up a iterating loop with url changing wih respect to this “transactionId” parameter, returned in each query’s response. But doesn’t work properly. Well, this is not your part, but if you know someone who has enough knowledge in powerBI to do this it would be great.

Franck

[Tim_hms]

Hi Franck I’m going to move this one to a new post just so other people might be able to find it easier if they’re running into issues with Power Bi

[Tim_hms]

Hi Franck

Have you seen this Tech Forum page from eWON that talks about setting up a flexy with Power BI?

https://techforum.ewon.biz/thread-529.html

I was talking with one of the people that worked on this and he was saying that he’s also seen people use Microsft Azure IOT Hub to transfer between Flexy and Power BI

[Franck]

Hi Tim,

Yes, I already see this forum page. This method (creating JSON in Flexy and then sending it to powerBI server), is very simple to perform and works very good. We need to create a “streaming Dataset” in PowerBI and fill it with the created JSON.

The problem with this method is that we can connect PowerBI report with only one “streaming Dataset”. They call this connexion “Direct Connexion” (something like this). I would like to have 2 different “streaming Datasets” (1 for Tags published every hour, and 1 for Alarms published on alarm change).

[Franck]

Here are my PowerBI query and loop :

Query function :

Loop to perform query until “moreDataAvailable” is equal “null” :

The result of that is the 3000 first data of DataMailBox, and not every pages (one page = 3000 data).

Franck

[Tim_hms]

Could you try and describe a little more specifically what you’re looking for with these calls? I don’t think that we’ll be able to have 2 different streaming datasets but I think we may be able to find a work around. This M2Web API reference sheet may be useful as well.

rg-0005-00-en-reference-guide-for-dmweb-api[1].pdf (697.5 KB)

[Franck]

Tim,

Thank’s for the help.

I want to have data from Tags and alarm in PowerBI.
I want the less transition possible between my Flexy, and Power BI (final destination of my data).
If possible, Tags retrieved periodically and alarm retrieved when value of alarm changes.
What do you propose ?

• 1st solution - 0 transition between flexy and powerbi, quick to implement (best option) :

sending HTTP request from the Flexy to implement data set in powerBI. The same way Simon did it in the techforum.post you send me. The difference is that I made one Json for Alarms (ONALARM or ONCHANGE), and one Json for Tags (ONTIMER)
I created 2 different streaming Datasets because I didn’t find an other way to follow my conditions. Maybe there is an other way, without creating this type of dataset?

• 2nd solution - 1 transition (still good option because DataMailBox is automaticaly filled) :

Using Power Query to retrieve data from DatamailBox (directly).

Picture from 1st query (and response on the right) :

Picture from 2nd query (and response on the right)

I made up a function to get data from DMBox. Function has one parameter : “transactionId”

I get :
_“DATA”: my data, as a list
_NEXT : the transaction ID I have to put in next url to have the next page of data from DMBox, and so on (this is explained in the document you posted).
_ISMORE : my “condition”. If ISMORE = TRUE, I still have more data to retrieve.

Now, I need a loop that makes a new request until ISMORE is not equals TRUE. This desired loop would use NEXT as parameter for the next request function. Finally, the desired loop will only keep the DATA of every function call. This is where I’m stuck.
There is not a real loop function in PowerQuery (like do while in other languages). So I found a function that should do a loop : List.Generate(). I put the code I made in PowerQuery in last post.

I explained everything, if you have any suggestion, I would be thankfull.

Franck

[Tim_hms]

Hi Franck,

I haven’t had the chance to try it out myself yet, but I think this should be possible to do what you’re mentioning with an onchange for alarms and a time interval for tags based off of these sections below with the M2Web API

[Franck]

Hi Tim,

Just want to add some news about this topic.
One of my colleagues found out how to do the loop in powerBI. I post the code here, it could be usefull one day.

Request function :

Loop :

Result is :

So the result is every pages that are available in DataMailBox. Here I have 5 pages of DMBox records.

Then you need to use PowerBI to transform your data.
Exemple of data transformation to obtain something understandable :

Franck

[Tim_hms]

Thanks for the update Franck, this will be very useful for people starting out with Power BI