Is Power BI the way to go

[Mikko2]

Hi!

I'm new to Power BI and I have been trying produce a report time and time again running to obstacles.

The case is this. I have a solution which stores data to SQL Server in JSON format. The JSON is packed (zipped). Categorational information is stored in other tables on the same SQL server. The solution itself handles one of these JSON's at a time. The need is now to report specific JSON node's changes between these JSONs. The categorational and navigational data should be first shown to user so he/she can drill into select specific JSONs. For this originally I thought using direct query. Based on the selections (or filterings if you will) the report (Power BI) would return multiple JSONs which would then be processed in Power BI to show differences/changes to the user. 

After a bit of trial and googling I realized that I cannot unzip the JSONs when the data comes via direct query. Then I thought for the JSON data I'd use rest APIs from the solution with parameterized calls which in turn would return already unzipped JSON data. Only the categorizational data would come via direct query. Then I realized that POST calls to REST can't be done with authentication from Power BI. Now I'm having a hard time parameterizing the Power BI web query GET call with the categorizational data coming via direct query. Last I ran into this error: "Formula.Firewall: Query 'testQuery' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination." Running into limitations time after time I started to think that maybe Power BI isn't meant for this kind of reporting or at least all data should already nicely organized in a dw to easily query from.

My original idea was to utilize Power BI as it offers nicely customizable table views for clicking around data easily. The users already use Power BI reports so the environment would be familiar. I know I don't have really any need for charts or such visualizatoins of the data which is where Power BI shines. 

[parry2k]

@Mikko2 power bi has many components and one of the major part of it is power query which allows to connect to multiple data sources and transform the data. Yes there are limitation with direct query as in that case power bi doesn't hold the data but just meta data.

The firewall issue you run into is privary setting and you need set the privacy level for your data sources and it should work. it is to avoid to leak sensitive data and combine with public data. there are many posts on firewall and privacy level and if you google it, you will get the answer and hope that will get you going.

[Mikko2]

Ok thanks, I got rid of that error only by setting ignore privacy levels for the current file. I hope this works from the service too. Setting equal privacy settings to the sources in use didn't seem to have an affect.

After getting that working I noticed that with the refresh preview in query editor for some reason the query is calling the api multiple times in a row. Here's my query:

let    
    Source = Json.Document(Web.Contents("http://localhost:4000/api/platform/request/" & Param1)),
    AllProcesses = Source[AllProcesses],
    jsonData = AllProcesses,
    #"Converted to Table" = Table.FromList(jsonData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Key", "Value"}, {"Column1.Key", "Column1.Value"})
in
    #"Expanded Column1"

It doesn't seem to matter whether there are any visualizations in the desktop side or any less steps in the power query. And the amount of how many times the call happens seems to vary from one to three.

It seems that the parameters can't be straight selected from the report (which would be best), but is there any way to populate the parameter list by selections made on the report data?

Or do you happen to know is it possible to use authentication with web.Contents and Content header (post request)? If I could somehow get the post request content from the users selections on the report..