Unpack nested API response to separate tables, in Pro workspace, without replicating API call

[darrenfishell]

I have a query to an API that includes a nested column that contains multiple records for each record in my response. 

I'd like to break these out into separate tables, given the different granularity, but I am also working with a Pro license and therefore cannot use computed entities. 

I've also noticed that dataflows do not preserve the contents of record or list columns for unpacking within a dataset pulling from that Dataflow (this would also solve my problem). 

Is there any way to either: a) have a staging query that pulls in the API content or b) retain the Record or List column content from the Dataflow?

[v-jingzhang]

Hi @darrenfishell 

You can Duplicate or Reference the API response query to create multiple queries in PQ in Power BI Desktop, and expand it to different granularity. Then publish this dataset to a workspace. If you want to use separate tables from this dataset in other reports, connect this dataset with DirectQuery mode. This is the only workaround I can think of. 

Best Regards,
Community Support Team _ Jing

[darrenfishell]

Thank you! As I understand it, each query that references the API response query will run that full script each time. So, if I had three references, it would run through the API request three times, correct? 

I'd like to just hit the API once, have it stored in local memory and then work to unpack that local copy in subsequent steps, if that's possible without computed entities.