cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

azure data lake store data in Power BI reports

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?

 

Objavro.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:

 

pqLogFirstHeaderoneSecondPhaseCNVoltageoneSecondPhaseAVA
2016-05-23T1527346622
2016-07-23T1557366622


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

 

 

2 REPLIES 2
Microsoft
Microsoft

Hi @rkanumuri,

 

I would suggest you try out the official connector.

azure_data_lake_store_data_in_Power_BI_reports

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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. 

 

 2018-03-28 (1).png

 

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!

 

2018-03-28.png

 

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

 

2018-03-28 (2).png

 

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors