Dynamically Pivot Multiple Columns in excel-CodePudding

I have the following data which I wish to Pivot to produce the table on the right. (Note this data has been manually entered and it's possible there are errors)

I'm unsure if Pivoting is the right way to go about this but it would achieve the desired effect if I only had one column.

The source data has already had a series of transformations applied to simplify to this stage however I am now studying trying to achieve the table on the right.

I appreciate I could create a series of custom columns by I would like this table to work dynamically such that if a user were to create a different Dose Descriptor a new column would dynamically be generated. For simplicity again I have only used NOAEL, DNEL and ADI as the dose descriptors but others e.g. TUL exist.

I will update any progress I make here but unfortunately have only got confused trying to do this.

Data:

CAS Number

77-92-9
77-92-9
106-24-1
106-24-1
106-24-1
7632-00-3
107-41-5
107-88-1
107-88-1
107-88-1
497-19-8
497-19-9
1300-72-7
99-76-3

Chemical Name

citric acid/ 2-Hydroxypropane-1,2,3-tricarboxylic acid
citric acid/ 2-Hydroxypropane-1,2,3-tricarboxylic acid
GERANIOL
GERANIOL
GERANIOL
Sodium nitrite
2-Methyl-2,4-pentanediol
1,3-Butylene glycol
1,3-Butylene glycol
1,3-Butylene glycol
sodium carbonate
sodium carbonate
Sodium xylenesulfonate
Methyl 4-hydroxybenzoate

Dose Descriptor

NOAEL
DNEL
NOAEL
DNEL
ADI
DNEL
DNEL
NOAEL
DNEL
ADI
ADI
NOAEL
NOAEL
DNEL

CodePudding user response：

Load data into powerquery using data ... from table/range [x]headers

Click select the first 4 columns, right click, unpivot other columns

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

= [Dose] & " " & [Attribute]

right click and remove dose column

right click and remove attribute column

click select the custom column and transform ... pivot column ..

use VALUE as the values column, advanced, don't aggregate

file .. close and load

sample code:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"No.", Int64.Type}, {"CAS Number", type text}, {"Chemical name", type text}, {"Dose", type text}, {"Oral", Int64.Type}, {"Inhale", Int64.Type}, {"Dermal", Int64.Type}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"No.", "CAS Number", "Chemical name", "Dose"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each [Dose] & " " &[Attribute]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Dose", "Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value")
in  #"Pivoted Column"