Originally posted on: http://geekswithblogs.net/LifeLongTechie/archive/2018/05/04/244683.aspxSQL 2016 or later and SQL Azure both support JSON function in native SQL. However, people using SQL 2014 or below are out of luck.
- New PowerBI file >> Get Data
- 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
- In PowerQuery Editor, right-click on the column we want to parse JSON, select "Transform" >> "JSON"
- 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"
- When we are done with the dataset, "Close and Apply"
- The JSON fields are now available to report on.
This post first appeared on , please read the originial post: here