SOLVED

Summarizing of text data in pivot tables.

%3CLINGO-SUB%20id%3D%22lingo-sub-1846943%22%20slang%3D%22en-US%22%3ESummarizing%20of%20text%20data%20in%20pivot%20tables.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1846943%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20task%20involves%20manipulation%20of%20gene%20sequencing%20data.%26nbsp%3B%20The%20description%20of%20the%20task%20is%20simplified%20to%20focus%20on%20key%20issues.%3C%2FP%3E%3CP%3EI%20have%20text%20data%20relating%20to%20a%20single%20gene%20that%20is%20spread%20over%20multiple%20rows.%26nbsp%3B%20The%20test%20strings%20are%20quite%20long%20and%20of%20variable%20length.%26nbsp%3B%20In%20the%20example%20I%20abbreviate%20the%20strings%20to%20a%2C%20b%2Cc%20etc%3C%2FP%3E%3CP%3EI%20need%20to%20transpose%20the%20text%20data%20relating%20to%20a%20gene%20so%20that%20there%20is%20one%20row%20per%20gene%20and%20the%20text%20data%20is%20stores%20as%20separate%20columns%20ie%3C%2FP%3E%3CP%3EGene%20Row%20Data%3C%2FP%3E%3CP%3EGN1%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20a%3C%2FP%3E%3CP%3EGN1%26nbsp%3B%26nbsp%3B%202%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20c%3C%2FP%3E%3CP%3EGN1%26nbsp%3B%26nbsp%3B%203%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20d%3C%2FP%3E%3CP%3EGN2%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20a%3C%2FP%3E%3CP%3EGN2%26nbsp%3B%26nbsp%3B%202%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20b%3C%2FP%3E%3CP%3EGN2%26nbsp%3B%26nbsp%3B%203%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20e%3C%2FP%3E%3CP%3EGN2%26nbsp%3B%26nbsp%3B%204%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20f%3C%2FP%3E%3CP%3EGN3%26nbsp%3B%26nbsp%3B%201%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20x%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDesired%20outcome%20%3A%3C%2FP%3E%3CP%3EGene%20Text1%20Text2%20Text3%20Text4%3C%2FP%3E%3CP%3EGN1%26nbsp%3B%26nbsp%3B%20a%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20c%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20d%3C%2FP%3E%3CP%3EGN2%26nbsp%3B%26nbsp%3B%20a%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20b%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20e%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20f%3C%2FP%3E%3CP%3EGN3%26nbsp%3B%26nbsp%3B%20x%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20transpose%20function%20doesn't%20work%20for%20me%20as%20it%20transposed%20the%20entire%20array.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20had%20high%20hopes%20for%20the%20Pivot%20Table%2C%20but%20it%20doesn't%20support%20presentation%20of%20text%20data%20within%20the%20body%20of%20the%20Pivot%20Table.%26nbsp%3B%20The%20functions%20COUNT%2C%20SUM%2C%20MIN%2C%20MEAN%20etc%20only%20work%20for%20numeric%20data.%26nbsp%3B%20I%20had%20hoped%20the%20MIN%20or%20MAX%20function%20would%20work%20for%20test%20data%20but%20it%20returns%20a%20value%20of%200.%26nbsp%3B%20See%20attached%20screen%20shot.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20me%20achieve%20my%20goal%2C%20using%20Pivot%20Table%20or%20other%20method%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1846943%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1847649%22%20slang%3D%22en-US%22%3ERe%3A%20Summarizing%20of%20text%20data%20in%20pivot%20tables.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1847649%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F855128%22%20target%3D%22_blank%22%3E%40DavidBridges%3C%2FA%3E%26nbsp%3BThis%20becomes%20easy%20with%20Get%20%26amp%3B%20Transfor%20Data%20(a.k.a.%20Power%20Query).%20See%20attached%20workbook%20based%20on%20you%20example.%20This%20will%20work%20particularly%20well%20on%20large%20data%20sets.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-11-03%20at%2007.48.54.png%22%20style%3D%22width%3A%20614px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F231115iE05436FDA89BD959%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202020-11-03%20at%2007.48.54.png%22%20alt%3D%22Screenshot%202020-11-03%20at%2007.48.54.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1847723%22%20slang%3D%22en-US%22%3ERe%3A%20Summarizing%20of%20text%20data%20in%20pivot%20tables.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1847723%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F855128%22%20target%3D%22_blank%22%3E%40DavidBridges%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20do%20that%20with%20PivotTable%20you%20need%20to%20add%20data%20to%20data%20model%20creating%20it%20and%20add%20measure%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3EMeasure%3A%3DCONCATENATEX(Table1%2CTable1%5BData%5D)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20use%20it%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20575px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F231120i4E21F0FBA5BA815F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1849557%22%20slang%3D%22en-US%22%3ERe%3A%20Summarizing%20of%20text%20data%20in%20pivot%20tables.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1849557%22%20slang%3D%22en-US%22%3EThanks%20for%20your%20help.%20Not%20what%20I%20would%20call%20intuitive%20but%20it%20works.%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

My task involves manipulation of gene sequencing data.  The description of the task is simplified to focus on key issues.

I have text data relating to a single gene that is spread over multiple rows.  The test strings are quite long and of variable length.  In the example I abbreviate the strings to a, b,c etc

I need to transpose the text data relating to a gene so that there is one row per gene and the text data is stores as separate columns ie

Gene Row Data

GN1   1     a

GN1   2     c

GN1   3     d

GN2   1     a

GN2   2     b

GN2   3     e

GN2   4     f

GN3   1     x

 

Desired outcome :

Gene Text1 Text2 Text3 Text4

GN1   a       c        d

GN2   a       b        e       f

GN3   x

 

The transpose function doesn't work for me as it transposed the entire array.

 

I had high hopes for the Pivot Table, but it doesn't support presentation of text data within the body of the Pivot Table.  The functions COUNT, SUM, MIN, MEAN etc only work for numeric data.  I had hoped the MIN or MAX function would work for test data but it returns a value of 0.  See attached screen shot.

 

Can anyone help me achieve my goal, using Pivot Table or other method?

4 Replies
Highlighted

@DavidBridges This becomes easy with Get & Transfor Data (a.k.a. Power Query). See attached workbook based on you example. This will work particularly well on large data sets.

Screenshot 2020-11-03 at 07.48.54.png

Highlighted
Best Response confirmed by DavidBridges (New Contributor)
Solution

@DavidBridges 

To do that with PivotTable you need to add data to data model creating it and add measure like

Measure:=CONCATENATEX(Table1,Table1[Data])

and use it as

image.png

 

Highlighted
Thanks for your help. Not what I would call intuitive but it works.
Highlighted

@DavidBridges , you are welcome