Preparation
You first need to set up SmileBack's Dropbox integration to get an automatically updated export of your reviews. Read about how to do that here.
Set up
- Open a new, blank Excel file.
- Go to the Data tab.
- Click on Get Data, then From File, then From JSON.
- Navigate to your Dropbox folder, then the "Apps" folder, then the "SmileBack" folder, select "SmileBack_RecentReviews.json".
- This will open the Power Query Editory, click on the View tab and then Advanced Editor
- Replace these last two lines of the query
in
with this
Source,
asTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
expandedReview = Table.ExpandRecordColumn(asTable, "Column1", {"id", "rating", "comment", "ticket", "contact", "company", "status", "tags", "has_marketing_permission", "viewed_on", "rated_on", "permalink", "last_modified"}, {"id", "rating", "comment", "ticket", "contact", "company", "status", "tags", "has_marketing_permission", "viewed_on", "rated_on", "permalink", "last_modified"}),
expandedTicket = Table.ExpandRecordColumn(expandedReview, "ticket", {"id", "title", "segment", "agents", "closed_on"}, {"ticket.id", "ticket.title", "ticket.segment", "ticket.agents", "ticket.closed_on"}),
expandedTicketSegment = Table.ExpandRecordColumn(expandedTicket, "ticket.segment", {"id", "name"}, {"ticket.segment.id", "ticket.segment.name"}),
expandedTicketAgentList = Table.TransformColumns(expandedTicketSegment, {"ticket.agents", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
expandedContact = Table.ExpandRecordColumn(expandedTicketAgentList, "contact", {"id", "name", "email"}, {"contact.id", "contact.name", "contact.email"}),
expandedCompany = Table.ExpandRecordColumn(expandedContact, "company", {"id", "name"}, {"company.id", "company.name"}),
expandedTags = Table.TransformColumns(expandedCompany, {"tags", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
strictlyTypedColumns = Table.TransformColumnTypes(expandedTags, {{"rated_on", type datetimezone}, {"viewed_on", type datetimezone}, {"rating", Int64.Type}, {"id", Int64.Type}, {"ticket.closed_on", type datetimezone}, {"has_marketing_permission", type logical}, {"last_modified", type datetimezone}}),
sortedRows = Table.Sort(strictlyTypedColumns, {{"rated_on", Order.Descending}})
in
sortedRows - Click Done.
- The query will now show the expanded JSON file, click on the Home tab and then Close & Load.
- You will now see the data formatted as a table in your Excel workbook. You can then simply click on the Refresh button when you want to add new data to the table.
Comments
0 comments
Please sign in to leave a comment.