Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

Power BI Presenting JSON data from SQL 2014 Table

Originally posted on: http://geekswithblogs.net/LifeLongTechie/archive/2018/05/04/244683.aspx

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!


This post first appeared on , please read the originial post: here

Share the post

Power BI Presenting JSON data from SQL 2014 Table

×

Subscribe to

Get updates delivered right to your inbox!

Thank you for your subscription

×