Forum Discussion

Andrew__K's avatar
Andrew__K
Frequent Visitor
2 years ago
Solved

Convert column to rows.

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 192.378979/01/2023 11:00 AM
Cook Value 265.541569/01/2023 11:58 AM
Cooling Value (52°C)52.386939/01/2023 12:21 PM
Cooling Value (12°C)11.961549/01/2023 3:00 PM
Max Temp / End Of Cook93.807819/01/2023 11:02 AM
Cooling Value (5°C)4.999449/01/2023 4:40 PM

 

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

Cook Value 192.37897
Cook Value 265.54156
Cooling Value (52°C)52.38693
Cooling Value (12°C)11.96154
Max Temp / End Of Cook93.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 1Cook Value 2Cooling Value (52°C)Cooling Value (12°C)Max Temp / End Of CookCooling Value (5°C)      
92.3789765.5415652.3869311.9615493.807814.999449/01/2023 11:00 AM9/01/2023 11:58 AM9/01/2023 12:21 PM9/01/2023 3:00 PM9/01/2023 11:02 AM9/01/2023 4:40 PM

 

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

Need Help
  • justinmartin's avatar
    justinmartin
    2 years ago

    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

     

     

1 Reply

  • justinmartin's avatar
    justinmartin
    Microsoft Employee
    2 years ago

    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