azure data lake store data in Power BI reports

[rkanumuri]

We have an Azure Data Lake Store which receives data from the EventHub every minute. We are trying to generate a report in Power BI Desktop application from this data. The data file in the Data Lake Store is a binary file with xx.avro extension as shown below. My question is, how can we convert this data into BI Table format to generate a graphical report?

Obj
avro.codecnullavro.schemaÏ{"type":"record","name":"EventData","namespace":"Microsoft.ServiceBus.Messaging","fields":[{"name":"SequenceNumber","type":"long"},{"name":"Offset","type":"string"},{"name":"EnqueuedTimeUtc","type":"string"},{"name":"SystemProperties","type":{"type":"map","values":["long","double","string","bytes"]}},{"name":"Properties","type":{"type":"map","values":["long","double","string","bytes","null"]}},{"name":"Body","type":["null","bytes"]}]}∆‡∏{'Oá[∏˝îÌT@‡Q,4048(3/27/2018 3:26:50 PMContentType>application/json; charset=UTF-8Postman-Token*****Ï&{
   "maximumBlockWindowPositiveWatt":"12804.00",
   "oneSecondCalculatedNeutralCurrent":"112.00",
   "oneSecondBlockTimeStamp":"2017-07-21T13:56:56.031-04:00",
   "pqLogFirstHeader":"2016-05-23T15:14:04.93-0400",
   "oneSecondPhaseAPowerFactor":"1.00",
   "flickerLogLastHeader":"2017-07-21T13:20:01.02-0400",
   "oneSecondThreePhasePowerFactor":"1.00",
   "blockWindowAverageBlockTimeStamp":"2017-07-21T13:56:56.032-04:00",
   "currentTime":"2017-07-21T13:56:56.033-04:00",
   "oneSecondPhaseCNVoltage":"273.00",
   "oneSecondFrequency":"60.00",
   "maximumBlockWindowNegativeWatt":"18593.00",
   "oneSecondPhaseCWatts":"18930.00",
   "oneSecondPhaseABVoltage":"476.00",
   "oneSecondVoltageImbalance":"2.00",
   "demand":"0.00",
   "oneSecondPhaseAVA":"46622.00",
   "oneSecondThreeVAR":"-30043.00"
   }.6688(3/27/2018 3:30:14 PMContentType>application/json; charset=UTF-8Postman-Token*****Ï&{
   "maximumBlockWindowPositiveWatt":"32804.00",
   "oneSecondCalculatedNeutralCurrent":"312.00",
   "oneSecondBlockTimeStamp":"2017-09-21T13:56:56.031-04:00",
   "pqLogFirstHeader":"2016-07-23T15:14:04.93-0400",
   "oneSecondPhaseAPowerFactor":"1.00",
   "flickerLogLastHeader":"2017-08-21T13:20:01.02-0400",
   "oneSecondThreePhasePowerFactor":"1.00",
   "blockWindowAverageBlockTimeStamp":"2017-07-21T13:56:56.032-04:00",
   "currentTime":"2017-08-21T13:56:56.033-04:00",
   "oneSecondPhaseCNVoltage":"573.00",
   "oneSecondFrequency":"60.00",
   "maximumBlockWindowNegativeWatt":"38593.00",
   "oneSecondPhaseCWatts":"38930.00",
   "oneSecondPhaseABVoltage":"676.00",
   "oneSecondVoltageImbalance":"3.00",
   "demand":"0.00",
   "oneSecondPhaseAVA":"66622.00",
   "oneSecondThreeVAR":"-30043.00"
   }∆‡∏{'Oá[∏˝îÌT@

The Table format we are expecting has to look like:

pqLogFirstHeader	oneSecondPhaseCNVoltage	oneSecondPhaseAVA
2016-05-23T15	273	46622
2016-07-23T15	573	66622

Is there any way we can accomplish this in Power BI Desktop application? 

[v-jiascu-msft]

Hi @rkanumuri,

I would suggest you try out the official connector.

Best Regards,

Dale

[rkanumuri]

Hi @v-jiascu-msft,  Thank you for your response. I might be not clear in my earlier question. We were able to load the data successfully into Power BI from Data Lake Store. The question we have is on how we can convert the Binary data into a table format so that we can have reports from the data we receive every minute? A shown below in the first image, able to load the data. Each row has a Key/Value pair data received in "key":"value" format. When I generate a report out of this data, it comes as a single text in one field. 

 

So we transformed the data into a Record type to be able to click on the Expand Icon as shown in the below image. When we click on the expand icon, we hoped to see all the 'keys' as column names. but no luck!

This is how we would like to convert our data into (Table format)

Hope, we have a way to do this in Power BI??