Nov 02 2020 03:39 PM
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?
Nov 02 2020 10:50 PM
@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.
Nov 02 2020 11:35 PM
SolutionTo 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
Nov 03 2020 07:09 AM
Nov 02 2020 11:35 PM
SolutionTo 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