Unwrapping SteemSQL JSON columns using Power BI – 10 Easy Steps

Have you struggled to extract data from the JSON columns in SteemSQL when analyzing the Steemit Blockchain database?

Maybe, like me you are not proficient enough in SQL to run the query directly. Well to the rescue is the query editor in Power BI.

The Power BI Query editor runs on a language known as M. However, Power BI is a self-service business intelligence package which means that you should be able to transform data without the need to know code.

In this tutorial I will show you step by step how you can use Power BI to connect to SteemSQL database and parse the active_votes column in the Comments tables.

Step by Step

Step 1: Open Power BI and Select Get Data from SQL database

Step 2: In the query editor window enter the following
Server: sql.steemsql.com
Database : DBSteem

Step 3: Select Advanced options in the query editor to open the SQL code box. Copy the following code to the box and select OK

   SELECT
     author,
     net_votes,
     children,
     Category,
     Depth,
     active_votes
      FROM
    Comments WITH (NOLOCK)
   where    created >= CONVERT(datetime,'11/01/2017')
   AND created< CONVERT(datetime,'11/02/2017')

The query editor will now run and you will be returned with the table of data containing the selected columns from the Comments table between the set dates.

Step 4 : You will then be given a preview of your data. Select Edit

This will load the data into the query editor for further transformation.

Step 5: Select the column active_votes and in the transform ribbon select parse JSON

This will convert your active_votes column to a list

Step 6: Expand the list to new rows by click on the cross-hair in the column header

This will convert your list to Records.

Step 7: Expand the Records again by clicking on the cross-hair in the column header. Uncheck user original column name as prefix and select ok.

You will now be returned with the JSON parsed into columns in your data set.

Step 8: Ensure that your new columns of data are set to the correct data type. Select any columns that require changing and from the transform ribbon you can select the data type

Step 9: Rename your query. You can do this on the right side of your screen under Query Settings and Name

Step 10: Carry out any further transformations you require on the data before loading it into the data model for further analysis with DAX or visual presentations.

Final Code

The final M code produced by Power BI is

 let
 Source = Sql.Database("sql.steemsql.com", "DBSteem", [Query="SELECT#(lf)    author,#(lf)    
 net_votes,#(lf)    children,#(lf)    Category,#(lf)    Depth,#(lf)    active_votes#(lf)        FROM#(lf)    
 Comments WITH (NOLOCK)#(lf)where    created >= CONVERT(datetime,'11/01/2017')#(lf)AND 
  created< CONVERT(datetime,'11/02/2017')#(lf)"]),
  #"Parsed JSON" = Table.TransformColumns(Source,{{"active_votes", Json.Document}}),
  #"Expanded active_votes" = Table.ExpandListColumn(#"Parsed JSON", "active_votes"),
  #"Expanded active_votes1" = Table.ExpandRecordColumn(#"Expanded active_votes", 
  "active_votes", 
  {"voter", "weight", "rshares", "percent", "reputation", "time"}, {"voter", "weight", "rshares", 
  "percent", 
  "reputation", "time"}),
  #"Changed Type" = Table.TransformColumnTypes(#"Expanded active_votes1",{{"voter", type 
  text}})
  in
  #"Changed Type"

Invitation

I would invite someone with SQL knowledge to write this query in SQL and share it below

If you have struggles with extracting this data from the database and you have found this tutorial of use, please do comment below.

** I am part of a Steemit Business Intelligence community. We all post under the tag #BIsteemit. If you have an analysis you would like carried out on Steemit data, please do contact me or any of the #bisteemit team and we will do our best to help you...

You can find #bisteemit on discord https://discordapp.com/invite/JN7Yv7j
**

---

Posted on Utopian.io - Rewarding Open Source Contributors

---