Automatically reorganizing large Excel table to separate each column into multiple columns based on grouping variables

Question

I have an Excel table with data organized such that each row is a sample and each column has a different property of that sample. However, I need to reorganize it so that it works with GraphPad Prism.

Currently the data is organized like this:

Sample ID	Exposure Level	Drug	Score 1	Score 22
101	1	A	0.675815	0.17351
102	1	B	0.276413	0.677079
103	2	A	0.914725	0.387529
104	3	A	0.504221	0.135295
105	3	B	0.963684	0.710081
106	2	B	0.964099	0.146872

And I want to make a box and whisker plot showing the score of each exposure level, like this:

I need to do this including all the samples and then again for just drug A and just drug B.

However, in order to do that in Prism, to my knowledge, each combination of variables you want needs to have in own column, like this:

Score 1 Exposure 1	Score 1 Exposure 2	Score 1 Exposure 3	Score 1 Exposure 1 (Just Drug A)	Score 1 Exposure 2 (Just Drug A)	Score 1 Exposure 3 (Just Drug A)	etc.
0.675815	0.914725	0.504221	0.675815	0.914725	0.504221
0.276413	0.964099	0.963684

This would be easy enough to do manually if there were just one score column, but there are twenty-two, so I'd rather not. Is there some automated way I can reorganize the data table like this?

You can probably do this with Power Query, possibly with Power Pivot. But I don't understand how you get the results you show based on the original data set you are showing. Also, to make the data useful for troubleshooting, please edit your question to post it as text, perhaps using this Markdown Tables Generator — Ron Rosenfeld
@RonRosenfeld Thank you, I changed the tables to Markdown! As for the results, it's just box and whisker plots of the two Score 1 numbers that have Exposure Level as 1 vs. the Score 1 numbers that have Exposure Level as 2 vs. Score 1 numbers that have Exposure Level as 3 — user3316549

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2020-12-22T12:24:34

To create a Box & Whiskers graph similar to what you show,

merely use the Exposure Level for the x-axis and the Score 1 column for the y-axis

To create a table similar to the results you show, you can use Power Query.

I created it as a single table, with each row representing a drug. You can then filter it by drug for your drug specific results.

The MCode is commented so by reading the comments, and also looking at the Applied Steps window, I hope I was clear in what was going on.

Most of the MCode is generated from the UI, but, especially, the colNames and ExpandTableColumns steps near the end are manually entered. Otherwise the number of columns in the expansion would not be flexible.

MCode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    //Won't need ID column so get rid of it
    #"Removed Columns2" = Table.RemoveColumns(Source,{"Sample ID"}),

    //Unpivot the Score columns to put them in a single column
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns2", {"Exposure Level", "Drug"}, "Attribute", "Value"),

   //sort by Score, Attribute, Drug so the results will be properly ordered 
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Attribute", Order.Ascending}, {"Exposure Level", Order.Ascending}, {"Drug", Order.Ascending}}),

    //Create what will become a two line header column
    // and remove the originals
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Headers", each "Exposure " & Text.From([Exposure Level]) & "#(lf)" & [Attribute]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Exposure Level", "Attribute"}),

    //Move headers to first column
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Headers", "Drug", "Value"}),

    //Group by Drug
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Drug"}, {{"Grouped", each _, type table [Headers=text, Drug=text, Value=number]}}),

    //Add an Index column
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 0, 1, Int64.Type),

    /*From each grouped table, remove Drug Column 
        and remove Header column EXCEPT fromk the first table
        then Transpose each grouped table*/
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each 
            Table.Transpose(
                if [Index] = 0 then 
                    Table.RemoveColumns([Grouped],"Drug") 
                else 
                    Table.RemoveColumns([Grouped],{"Headers","Drug"}))),

    //Remove no longer needed Grouped and Index columns
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Grouped", "Index"}),

    //Expand the table columns, promote headers, and rename the drug column to get final results
    colNames = Table.ColumnNames(#"Removed Columns1"[Custom]{0}),

    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", colNames),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Custom", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"A", type text}, {"Exposure 1#(lf)Score 1", type number}, {"Exposure 2#(lf)Score 1", type number}, {"Exposure 3#(lf)Score 1", type number}, {"Exposure 1#(lf)Score 22", type number}, {"Exposure 2#(lf)Score 22", type number}, {"Exposure 3#(lf)Score 22", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"A", "Drug"}})
in
    #"Renamed Columns"

EDIT

@user3316549 commented below that he might have multiple entries for the same drug for the same Score/Exposure and wanted the results for each shown separately.

A Pivot table would be useful here, except a classic pivot table will only have a single entry for each intersection of Drug with Score/Exposure.

This problem is solved with a custom function for the pivot that adds an extra row when needed. The credits for that function are included and you can examine the link for a detailed explanation of the algorithm used for that part of the code.

The custom function is added as a blank query. You can name it what you choose and call it that way in your main code.

M Code

Main Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    //Unpivot the Score columns to put them in a single column
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {"Sample ID","Exposure Level", "Drug"}, "Attribute", "Value"),

   //sort by multiple columns so the results will be properly ordered to our liking
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",{{"Attribute", Order.Ascending}, {"Exposure Level", Order.Ascending}, {"Drug", Order.Ascending},{"Sample ID", Order.Ascending}}),

    //Create what will become a two line header column
    // and remove the originals
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Headers", each [Attribute] & "#(lf)" & "Exposure " & Text.From([Exposure Level])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Sample ID","Exposure Level", "Attribute"}),

    //custom pivot function for non-aggregation
    pivotAll = fnPivotAll(#"Removed Columns","Headers","Value")
in
    pivotAll

M Code

Custom Function named fnPivotAll

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

Automatically reorganizing large Excel table to separate each column into multiple columns based on grouping variables

1 Answers

EDIT