How to switch rows and columns in datatable in Canvas?

[YuWatanabe]

Hello.

I would like to get help with kibana Canvas.

I have a data table as below.

My goal here is to create a table like below. I want to create columns according to the value of tag_type.

shape.model    first     second     pending
null              0           0       8851
ahead_txt         1          25          0
billboard        25         368          0

My current query is below.

filters
| essql 
  query="SELECT shape.model, tag_type, COUNT(tag_type) FROM \"tags\" WHERE tag_type IS NOT NULL GROUP BY shape.model, tag_type"
| table perPage=25
| render

I am thinking of mapColumn might do the work but I cannot figure out the right syntax from the document.

https://www.elastic.co/guide/en/kibana/current/canvas-common-functions.html#_mapcolumn

I have also checked below blog but could not find related issue.

Here to Help... (An Elastic{ON} Canvas Story)

If I can do something like below but cannot figure out how.:

mapColumn "Status1" fn={if {getCell "tag_type" | eq "first"} then=GET COLUMN COUNT_tag_type_ else=0}

Could some one help me out to achieve my goal ?

Thanks,
Yu

[YuWatanabe]

I figured it out . There was a good example on this page

[tims (Tim Schnell)]

Glad you figured it out and that we had a doc that was helpful!