Analyse the JSON File with Power Query

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

In the last Post, I will explain how to analyze a JSON file that has been generated in the Sentiment Analysis process .

some explanation, this is a JSON file that contains the sentiment analysis for the comments one traveler put on the hotel website as below

The suite was awesome. We did not have much interaction with the staff. We did sleep on the queen size sofa bed in the living room instead of the queen size bed in the actual bedroom due to the temperature. It was very hot and humid. The air conditioner in the living room does not cool off the bedroom. This suite is very close to shopping and dining. After each day of adventures, we would walk to dinner at different joints. I will stay here again when I return.

the JSON file is like below

{“predictionOutput”:{“result”:{“sentiment”:”mixed”,”documentScores”:{“positive”:0.64,”neutral”:0.05,”negative”:0.31},”sentences”:[{“sentiment”:”positive”,”sentenceScores”:{“positive”:1.0,”neutral”:0.0,”negative”:0.0},”offset”:0,”length”:22},{“sentiment”:”neutral”,”sentenceScores”:{“positive”:0.01,”neutral”:0.83,”negative”:0.16},”offset”:23,”length”:48},{“sentiment”:”neutral”,”sentenceScores”:{“positive”:0.0,”neutral”:1.0,”negative”:0.0},”offset”:72,”length”:134},{“sentiment”:”neutral”,”sentenceScores”:{“positive”:0.03,”neutral”:0.93,”negative”:0.04},”offset”:207,”length”:26},{“sentiment”:”negative”,”sentenceScores”:{“positive”:0.01,”neutral”:0.06,”negative”:0.93},”offset”:234,”length”:69},{“sentiment”:”positive”,”sentenceScores”:{“positive”:0.92,”neutral”:0.08,”negative”:0.0},”offset”:304,”length”:48},{“sentiment”:”neutral”,”sentenceScores”:{“positive”:0.02,”neutral”:0.96,”negative”:0.02},”offset”:353,”length”:73},{“sentiment”:”neutral”,”sentenceScores”:{“positive”:0.03,”neutral”:0.92,”negative”:0.05},”offset”:427,”length”:37}]}},”operationStatus”:”Success”,”error”:null}

 

 

 

The related JSON file allocate an overall score to the whole comment, then for each sentence, you can get a separate sentiment score, the result in the JSON format is not able to analyze, so I decided to use Power Query to solve this problem

First Open Power BI desktop and navigate to Power Query, import the JSON file, then load the data, click on the record to expand it and to see the record and list. Right-click on both of them and add them as a separate query.

 

Then click on the To Table, and expand the records to see the inside, finally, you should see the 7 rows of data with label positive, negative and neutral, and the two last columns for the offset and length of each sentence.

 

Now we need to add the original text to merge them together, import the text file, then use the dot separator and Transpose to make them as a table. Then use the Add Column and add index column, to both the Sentence query and the hotel comment. Then in the Home Tab click on the Merge columns and merge them based on the Index

 

 

 

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Leila Etaati on LinkedinLeila Etaati on TwitterLeila Etaati on Youtube
Leila Etaati
Trainer, Consultant, Mentor
Leila is the first Microsoft AI MVP in New Zealand and Australia, She has Ph.D. in Information System from the University Of Auckland. She is the Co-director and data scientist in RADACAD Company with more than 100 clients in around the world. She is the co-organizer of Microsoft Business Intelligence and Power BI Use group (meetup) in Auckland with more than 1200 members, She is the co-organizer of three main conferences in Auckland: SQL Saturday Auckland (2015 till now) with more than 400 registrations, Difinity (2017 till now) with more than 200 registrations and Global AI Bootcamp 2018. She is a Data Scientist, BI Consultant, Trainer, and Speaker. She is a well-known International Speakers to many conferences such as Microsoft ignite, SQL pass, Data Platform Summit, SQL Saturday, Power BI world Tour and so forth in Europe, USA, Asia, Australia, and New Zealand. She has over ten years’ experience working with databases and software systems. She was involved in many large-scale projects for big-sized companies. She also AI and Data Platform Microsoft MVP. Leila is an active Technical Microsoft AI blogger for RADACAD.

Leave a Reply