How to "Unpivot" by column group based on condition

[Andy_Ng]

Hello,

I have a question that I really need help from experts.

I'm entering data for a table of values and temperatures, where:

• The device code is used as the column name containing the value.
• The device code paired with _T is the name of the column containing the temperature.

That table has the structure as below.

Date	P01	P02	P01_T	P02_T	P03	P04	P03_T
1/1/2023	1	3	23	30	3	7	27
1/2/2023	3	4	22	29	5	5	28
1/3/2023	2	5	25	29	3	3	26

Then I need to transform that "Input" table into a data structure like the "Output" table below but haven't found a way to do it.

Date	Code	Value	Temp
1/1/2023	P01	1	23
1/1/2023	P02	3	30
1/1/2023	P03	3	27
1/1/2023	P04	7
1/2/2023	P01	3	22
1/2/2023	P02	4	29
1/2/2023	P03	5	28
1/2/2023	P04	5
1/3/2023	P01	2	25
1/3/2023	P02	5	29
1/3/2023	P03	3	26
1/3/2023	P04	3

Please help. Thank you very much.

[wdx223_Daniel · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Custom1 = Table.UnpivotOtherColumns(Source,{"Date"},"x","y"),
    Custom2 = Table.SplitColumn(Custom1,"x",each List.FirstN(Text.Split(Text.Replace(_,"_T","_Temperature")&"_Value","_"),2),{"Name","x"}),
    Custom3 = Table.Pivot(Custom2,{"Value","Temperature"},"x","y",each _{0}?)
in
    Custom3

[Andy_Ng]

Thank you, Daniel. I'm not proficient in coding, but from your code, I have grasped the concept. It's extremely interesting. Thanks a lot.

[HotChilli]

Unpivot all columns except date.

Split the Attribute column on '_'

Duplicate the table.

In version 1 of the table, keep only the rows that do not have a T value (Filter this from the column header)

In version 2 of the table, keep only the rows that have a T value.

Merge the tables together on date and attribute (Make sure you get the join right for your data - looks like a left join from the non-T table)

[Andy_Ng]

Thanks, HotChilli. The idea is very interesting, and I'll give it a try. I'd also like to share Daniel's idea:

• Unpivot columns that are not Date; This will create two new columns, Attribute containing the column name, and Value containing the value.
• Add a new column (e.g., named MyColumnName) with content as Temperature for data with _T and Value for data without _T.
• Remove _T.
• Run PivotTable again using data in the MyColumnName column as the column name and the Value column for the values.

Thank you for your help