cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Timmofo Frequent Visitor
Frequent Visitor

Creating a flat table from JSON import

Hello everyone,

 

I searched long and hard for an answer to my question. I did find some threads which were similar but did not find the exact same problem as mine. 

 

My goal is to have a table with a column containing the ticket ID (primary ID) and all free fields as columns in a flat structure so I can do visualizations for our tickets in Zendesk. This can be seen in the sketch below.

 

 Ticket ID |  Free field 1 | Free field 2 | Free field 3

------------------------------------------------------

      1        |        x           |       true      |        2

      2        |        y           |       true      |       13

      3        |        x           |       true      |        3

      4        |        x           |       true      |        3

      5        |        y           |       false     |        7

 

I imported our Zendesk data through the "get data" function and in the Ticket.table we have two columns. One contains the Ticket ID and another column contains the free field data. This is all good so far. However, the free field data is being imported as a list of records. If I then use the expand function, the table I want will be formatted as follows:

 

Ticket ID |  Free field.title | Free field.value

----------------------------------------------

      1        |   Free field 1   |       x    

      1        |   Free field 2   |       true     

      1        |   Free field 3   |       2    

      2        |   Free field 1   |       y  

      2        |   Free field 2   |       true    

      2        |   Free field 3   |       13     

                               etc.

   

While this kind of works, I run into some troubles with my visualizations due to the fact that I want to count tickets which must meet multiple requirements (for example: count ticket where (Free field 1 = x) AND (free field 3 > 5)). When I do this, I always get the value 0. This makes sense because the ticket has no flat structure and therefore the multiple parameters cannot be used. 

 

I would like to be able to fix this in the "edit query" instead of with DAX if this is possible, this will save me a lot of time with the many visualizations I have to make in the following months.

 

Please understand that I am a newbie, but not scared to put in some work to get it working. If my description is not fully clear, please let me know and I'll add it in the comments.

 

Could anyone please guide me in the right direction?

 

With kind regards,

Tim

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
quentin_vigne Senior Member
Senior Member

Re: Creating a flat table from JSON import

Hi @Timmofo

 

You need to go to power query editor and unpivot your column

2016-04-07_00h40_54

 

- Quentin

2 REPLIES 2
Highlighted
quentin_vigne Senior Member
Senior Member

Re: Creating a flat table from JSON import

Hi @Timmofo

 

You need to go to power query editor and unpivot your column

2016-04-07_00h40_54

 

- Quentin

Timmofo Frequent Visitor
Frequent Visitor

Re: Creating a flat table from JSON import

Thank you so much. I tried this before, but made a mistake by aggregating.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 30 members 766 guests
Please welcome our newest community members: