CODEWITHSUNDEEP
powerbipowerquerypowerbi-desktop
Luiz Carlos
Luiz Carlos

Reputation: 99

PowerQuery - Transposing a table record

I'm new to powerBi, did a couple online lessons but I'm struggling with this.

My company currently uses Zendesk ticket system, and I've used the export function to get all data on a XML file.

So far so good, but we use custom fields and after importing into PowerBI, the custom fields for each ticket appear as as "table" record, and I cannot do my filters correctly.

My goal is to normalize the table, having all the custom fields as columns.

This is my current status:

enter image description here

And below is a detailed example of my goal.

Can somebody point me a few hints on how to extract the table and transpose the records as new columns?

Just by expanding this column, it creates a lot of duplicated rows for each ticket.

enter image description here

Upvotes: 0

Views: 314

Answers (1)

horseyride
horseyride

Reputation: 21298

Add column ... custom column ... with formula

=Table.Skip(Table.Transpose([custom_fields]),1)

Then clicking the arrows at the top of the new column and expand the fields, then rename them

Sample code:

#"Added Custom" = Table.AddColumn(PriorStepName, "Custom", each Table.Skip(Table.Transpose([custom_fields]),1)),
// expand all column names
List = List.Union(List.Transform(#"Added Custom"[Custom] , each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(#"Added Custom" , "Custom", List,List)
in #"Expanded Data"

Upvotes: 1

Related Questions