Transform JSON which contains Column Definitions and Row Data into single table

[thnerd]

Hello! 

I am stuck... I have a JSON source which contains Column Definitions as well as Row Data and I need to combine the results into a single table. The data is always related. There is a caveat to this plea for help which is that this needs to be dynamic in the form of a function, all columns will need to be expaned automatically etc.  I have shortened this example json for ease/brevity but there are many tables and columns which I will not know the contents of before run-time. *Bonus would be to assign the Power Query Table datatype based on the Column Definition "type", but that is a wishlist item, not required to get me where I need to go.

I have attached some screenshots that I thought might be helpful as well as a sample JSON file for reference.

Thanks in advance for any assistance!

JSON File

[v-jiascu-msft · Accepted solution]

Hi @thnerd,

Please download the demo from the attachment. It's dynamic. Please refer to the steps in the Query Editor for details. Some steps need customized formulas. Please double click the steps for details. 

Best Regards,
Dale

[thnerd]

Thank you Dale! It might be worth adding the name of the "type" of JSON that is represented in this example to this post, however I do not know what that is called. (i.e. multidimensional etc - though I dont think that is what this one is). If you  or anyone knows i would be happy to include in the original post.

Thanks again! ~thn

[v-jiascu-msft]

Hi @thnerd,

Would you like to change the column type based on the "type" of JSON file? Or you just want to show it in the data. If it's the former one, I think it would be complicated. Because these types are different from Power BI. Please refer to the snapshot below. If it's the latter one, how should it be?

Best Regards,
Dale

[thnerd]

Hi Dale, Ideally yes, even with some logic to set "Numeric" to decimal or something would work.

What I was referring to ealier was the "type" of JSON schema the example is in. Thought it might be worth including in the description to help others in the future.

I am all set, thanks again!

~thn