Convert column to rows.

[Andrew__K]

Hi all, trying to convert a column to rows so that the data can then be transposed as its own column.

Below is an example set of the data, basically, I want the date/time column to be added as their own rows.

Cook Value 1	92.37897	9/01/2023 11:00 AM
Cook Value 2	65.54156	9/01/2023 11:58 AM
Cooling Value (52°C)	52.38693	9/01/2023 12:21 PM
Cooling Value (12°C)	11.96154	9/01/2023 3:00 PM
Max Temp / End Of Cook	93.80781	9/01/2023 11:02 AM
Cooling Value (5°C)	4.99944	9/01/2023 4:40 PM

Below is an example of what I am trying to acheive. 

Cook Value 1	92.37897
Cook Value 2	65.54156
Cooling Value (52°C)	52.38693
Cooling Value (12°C)	11.96154
Max Temp / End Of Cook	93.80781
Cooling Value (5°C)	4.99944
	9/01/2023 11:00 AM
	9/01/2023 11:58 AM
	9/01/2023 12:21 PM
	9/01/2023 3:00 PM
	9/01/2023 11:02 AM
	9/01/2023 4:40 PM

Which can then be transposed like below and the columns could then be renamed, re-ordered, etc...

Cook Value 1	Cook Value 2	Cooling Value (52°C)	Cooling Value (12°C)	Max Temp / End Of Cook	Cooling Value (5°C)
92.37897	65.54156	52.38693	11.96154	93.80781	4.99944	9/01/2023 11:00 AM	9/01/2023 11:58 AM	9/01/2023 12:21 PM	9/01/2023 3:00 PM	9/01/2023 11:02 AM	9/01/2023 4:40 PM

I feel like I'm missing a simple solution, could anyone point me the right direction? TIA

[justinmartin · Accepted solution]

Hey Andrew, 

Would something like this work?

let
    _source =
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "bY89CsJAEEavMmylEDY7szubnbTBUmzEJqQI+IMYExvBY3kGT+YGkmJDuine431T16oahgec2u59AVSZEtK2CFKMZ445GbKAWBqjmiyBKRKeNTtkv4A5zHB3728Tv2H6fattRDkmghebWFQSrlk4W4haPLJLLJ6H7dsPHC/PF+Sw689wuMI4dWStDqYIuPyHVidOLadFxKUpX7qYav4=",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t =
                    ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [ Attribute = _t, Value = _t, Time = _t ]
        ),
    _select_time =
        Table.SelectColumns (
            _source,
            { "Time" }
        ),
    _sort_time =
        Table.Sort (
            _select_time,
            { "Time", Order.Ascending }
        ),
    _rename_sort_column =
        Table.RenameColumns (
            _sort_time,
            {
                { "Time", "Value" }
            }
        ),
    _remove_time_from_source =
        Table.RemoveColumns (
            _source,
            { "Time" }
        ),
    _combine_tables =
        Table.Combine (
            {
                _remove_time_from_source,
                _rename_sort_column
            }
        )
in
    _combine_tables