CODEWITHSUNDEEP
jsonpowerbi
Priyanka Mane-Patil
Priyanka Mane-Patil

Reputation: 519

Split json data into new table to visualize data using PowerBI with a Direct Query connection

We connect PowerBI to a database using a live connection (i.e. Direct Query) where one tables' specific column contains dynamic list of data in JSON format as below:

row1 : {"models":[{"modelname":"name1","Count":value1}]}    
row2: {"models":[{"modelname":"name1","Count":value1},{"modelname":"name2","Count":value2}]}"

To make this JSON data useful, will have to split it and then use it. To do so, in import query connection, we want to do it as explained in: solution for import query connection

But that solution is not available in Direct Query connection.

Is there some workaround for this issue?

Upvotes: 2

Views: 2502

Answers (2)

Aku
Aku

Reputation: 802


I just wanted to mention that **the sql-dialect used in the "Advanced Options" were you write your Query has to be the one of your chosen database**.. I did assume that I had to go with t-SQL since PowerBI is a Microsoft-Product...which costed a lot of nerves!

Upvotes: 0

Leonard
Leonard

Reputation: 2578

DirectQuery means Power BI is directly connecting to your source database. It is not storing any data itself; all requests are sent back to your source database.

The Parsing JSON solution that you reference in your question manipulates the data after it arrives in Power BI (and then stores it locally). It's not an option for you in DirectQuery mode.

Two possibilities that are worth exploring:

  1. Switch to import mode, which will open up the full Power Query Formula Language to you (but this has a lot of other implications that may or may not mean this is a viable option for you - see documentation link below). I include this mainly for comprehensiveness.

  2. Rather than import the full table in Direct Query mode, click the 'Advanced options' option and use the database engine's built-in language to write a SQL query that will extract the data you want, in the way you want, before it reaches Power BI.

Write your own JSON parsing query Example of using SQL Server 2016's built-in JSON parsing tools in Power BI DirectQuery mode

For option #2, there may be a number of limitations or compromises you have to make. For example, you may need the extracted JSON to be a separate table to the rest of the columns in the source table. There is a 1 million row limitation. It may be that there's no easy way to extract out your data in the way you want using your database's language. (Note: I've never done this myself, so if after trying it, you have tips or pitfalls to share, please do post back here)

There's good documentation on the benefits & limitations of Direct Query mode here: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery/#benefits-of-using-directquery.

There's information on the SQL Server 2016 JSON functions starting here: https://blogs.technet.microsoft.com/dataplatforminsider/2016/01/05/json-in-sql-server-2016-part-1-of-4/ (if you happen to be using SQL Server 2016)

Hope this helps.

Upvotes: 2

Related Questions