Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
thnerd
Frequent Visitor

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

1 ACCEPTED SOLUTION
v-jiascu-msft
Employee
Employee

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

4 REPLIES 4
v-jiascu-msft
Employee
Employee

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.

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

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.