Advanced Transposing

[ulrich_schumann]

Hello,

today I received some Excel files where the standard transpose tool does not work for me.

The columns are like the following: Column name and example data

Topic | subtopic | Jul 2018 : percentage resolution | Jul 2018 : total resolution | Aug 2018 :  percentage resolution | Aug 2018 : total resolution |

Helpdesk | Tickets | 0.85 | 4636 | 0.91 | 3663 |

Helpdesk | FirstFix | 0.69 | 1990 | 0.65 | 1801 |

I need the following structure:

Topic | subtopic | Month Year | percentage resolution | total resolution |

Helpdesk | Tickets | Jul 2018 |  0.85 | 4636 |

Helpdesk | Tickets | Aug 2018  0.91 | 3663 |

Helpdesk | FirstFix | Jul 2018 | 0.69 | 1990 |

Helpdesk | FirstFix | Aug 2018 | 0.65 | 1801 |

Any ideas how to solve this?

[tcroberts · Accepted solution]

Hi,

I've attached a sample workflow here,

I achieved this by transposing as usual, with topic and Subtopic as keys, then using the TextToColumns to split on the ":", followed by a REGEX_Replace to clean up the columns. Afterwards, I used the CrossTab Tool, grouping by Topic, Subtopic and Name1 (which is the date in this case), with Name2 as the new column headers, and Value as the values. Name1 and Name2 are the first and second columns created by the TextToColumns Tool.

Let me know if you have any other questions about this workflow,

Cheers!

[ulrich_schumann]

Thanks tcroberts,

perfect - exactly what I need.