Power BI Presenting JSON data from SQL 2014 Table

SQL 2016 or later and SQL Azure both support JSON function in native SQL.  However, people using SQL 2014 or below are out of luck.

However, if we just need a quick report, PowerBI can provide that functionality.  Here are the steps:
  1. New PowerBI file >> Get Data
  2. Set up our SQL connection.  Because JSON parsing function is not supported natively, this connection cannot be Direct Query.  Even if we set it up as such, later steps will force PowerBI to change the connection type to "Import".
    Do not click "Load", click "Edit" to get into Query Designer
  3. In PowerQuery Editor, right-click on the column we want to parse JSON, select "Transform" >> "JSON"
  4. A new "expand" button will appear on the top right corner of column header.  Click on that and select the fields out of JSON we want to report on.  Then click "OK"
  5. When we are done with the dataset, "Close and Apply"
  6. The JSON fields are now available to report on.
Have fun!

Print | posted on Friday, May 4, 2018 9:54 AM

Feedback

No comments posted yet.

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski