Reply
Frequent Visitor
Posts: 3
Registered: ‎12-25-2018
Accepted Solution

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

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.jpgcoldef.jpgrowdef.jpgexcel.jpg

 

JSON File


Accepted Solutions
Community Support Team
Posts: 7,295
Registered: ‎05-02-2017

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

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. 

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

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Attachment

All Replies
Community Support Team
Posts: 7,295
Registered: ‎05-02-2017

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

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. 

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

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Attachment
Frequent Visitor
Posts: 3
Registered: ‎12-25-2018

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

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

Community Support Team
Posts: 7,295
Registered: ‎05-02-2017

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

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?

Transform-JSON-which-contains-Column-Definitions-and-Row-Data-into-single-table2

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor
Posts: 3
Registered: ‎12-25-2018

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

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