Table reference and duplicate generate multiple queries

[FrPol]

Hi all,

so I have this scenario:

Json datasource from web endpoint

I shall do a single call to the endpoint and return a big flat table, that i want to split in multiple tables.

I should avoid calling the endpoint multiple time due to thorttling policies that may cause the response to pass the 5minutes (inside the endpoint itself) and timeout the response.

I was looking into the option to use a single call and retrieve the big table, then reference\duplicate multiple times to get a decent relations ship structure.

Anyway monitoring the calls to the endpoint I see a connection coming for each duplicate\reference + the original table.

Is there any elegant way to slice the table after the data is downloaded locally from a single connection to the main table?

Tnx Fr

[Phil_Seamark]

If you call the table once using Power Query and then slice/dice up the table in DAX you will reduce the likelyhood of extra API calls.

I've seen PQ make multiple calls to the datasource when it seems it doesn't need to.

How complex are your data transformations?

[FrPol]

Hi Phil thanks.
I still looking into the transformations, but likely it Will only be something like:
Split table in 4 or 5 different ones
Create some aggregation functions to do simple calculus like evaluating some fields against other.

To say, i can can even keep the table flat, but is probably a 50+ fields and lose readability and make missing a real structure... Unless there is native format for a json response to include a data structure that pbi can parse from the begin as multiple tables structure.

Thanks Fr

[Phil_Seamark]

There is no json feature just yet, although interestingly you can build datasets in the Power BI web service API using JSON structure files.  

I can help with the DAX if you like but suggest you give me an example table with what your expected output is and I can suggest how it may be done.

[FrPol]

hi Phil thanks, currently the configuration is even more messed up 🙂 columns are not 50+ but 240+.

the json structure is like this one:

{
        "Identity":  "1234567890",
        "DisplayName":  "test test",
        "Alias":  "test",
        "Status":  "Completed", 
        "ST_Identity":  "1234567890",     
        "ST_CpuThrottle":  "0d 0h 0m 0s ms",
        "ST_DiskLatencyThrottle":  "0d 0h 0m 0s ms",
        "SStat_Identity":  "1234567890",
        "SStat_TotalTimeProcessingMessages":  "00d 00h 00m 30s 8295ms",
        "SStat_TimeInGetConnection":  "0d 0h 0m 0s ms",
        "SourceInfo":  {
                             "Current":  98,
                             "Average":  98,
                             "TotalRemoteCallDuration":  "00:01:26.2020244",
                             "Min":  98,
                             "Max":  98
                       },
}

Where colums starting with, like, sstat_* or ST_* are the ones to be set in different tables, as you can see, i am using the Identity field and replicate it in *_identity in order to keep it a reference between potential tables

thanks fr