Forum Discussion

pel20's avatar
pel20
Regular Visitor
7 months ago
Solved

unpivoting columns and creating datetime column

I have some power consumption data that is taken every 30 minutes in the following form:

 

How can I get this to display in two columns: a DateTime value, then a consumption value? If I unpivot the table, I get the following, but I'm not sure how to take the final step to combine the date value with the time values into a new column in the correct row for each value.

 

Thanks.

 

Need Help
  • danextian's avatar
    danextian
    7 months ago

    Hi pel20 

    The problem is you simply unpivoted the whole table as there is nothing inside {}. There is nothing in there that should be excluded from being unpivoted. Right-click Reading Date column and then unpivot other columns.

     

4 Replies

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    7 months ago

    Hi pel20 

    The problem is you simply unpivoted the whole table as there is nothing inside {}. There is nothing in there that should be excluded from being unpivoted. Right-click Reading Date column and then unpivot other columns.

     

  • hnguy71's avatar
    hnguy71
    Icon for Super User rankSuper User
    7 months ago

    Hi pel20 

     

    You'll need to adjust you formula to add the columns you want to remain grouped in the curly bracket. For example, in your case, just the 'Reading Date':

    Table.UnpivotOtherColumns(#"Removed Columns", {"Reading Date"}, "Attribute", "Value")
  • MasonMA's avatar
    MasonMA
    Icon for Super User rankSuper User
    7 months ago

    Hi, 

     

    In your Table.UnpivotOtherColumns() function, put "Reading Date" in { } as in, 

    = Table.UnpivotOtherColumns(Source, {"Reading Date"}, "Attribute", "Value")

     

    Or, you can paste below M in a blank query and see how this can be transformed, 

    let
    Source = Table.FromRows(
        {
            {"01/04/2026",0,23,25,27,30,28},
            {"02/04/2026",2,36,56,52,49,51},
            {"03/04/2026",1,18,22,24,26,29},
            {"04/04/2026",0,15,19,21,23,25},
            {"05/04/2026",3,40,45,48,50,53},
            {"06/04/2026",2,34,38,41,43,46},
            {"07/04/2026",1,20,24,26,28,31}
        },
        {"Reading Date","08:00","08:30","09:00","09:30","10:00","10:30"}
    ),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reading Date", type date}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(
        #"Changed Type",
        {"Reading Date"},
        "Time",
        "Consumption"
    ),
    #"Changed Time Type" = Table.TransformColumnTypes(#"Unpivoted Columns",{{"Time", type time}}),
    #"Added DateTime" = Table.AddColumn(#"Changed Time Type", "Reading DateTime", each [Reading Date] & [Time]),
    #"Removed Columns" = Table.RemoveColumns(#"Added DateTime",{"Reading Date","Time"})
in
    #"Removed Columns"