Pivot unpivot Timestamp Values

Hi Anonymous ,

Try the following code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY3NNY3MjAyVTA0sjIwACKFAF8lHSXn/NyCxLxKQyDTQM9IKVaHVkqNkJWamGJXakq0UkyFpsSbianUCEkpkqdw+olUlUbIKnE4FCOcCKhEtt2IaB/hURkLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Company = _t, Value = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type datetime}, {"Value", type number}}, "en-us"),
    #"Grouped Rows" = Table.Group(#"Changed Type with Locale", {"Date"}, {{"Count", each _, type table [Date=nullable datetime, Company=nullable text, Value=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ( [Count], "Index",1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Company", "Value", "Index"}, {"Custom.Company", "Custom.Value", "Custom.Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    #"Grouped Rows1" = Table.Group(#"Removed Columns", {"Date", "Custom.Index"}, {{"Count", each _, type table [Date=nullable datetime, Custom.Company=text, Custom.Value=number, Custom.Index=number]}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows1", {{"Date", type text}}, "pt-PT"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows1", {{"Date", type text}}, "pt-PT")[Date]), "Date", "Count"),
    #"Added Custom1" = Table.AddColumn(#"Pivoted Column", "Custom", each Table.FromColumns(
        (try Table.ToColumns([#"13/03/2025 12:00:00"]) otherwise Table.ToColumns( #table({"Date"}, {{null}})))
   & (try Table.ToColumns([#"13/02/2025 12:45:00"]) otherwise Table.ToColumns( #table({"Date"}, {{null}})))
   & (try Table.ToColumns([#"13/02/2025 12:55:00"]) otherwise Table.ToColumns( #table({"Date"}, {{null}})))
   & (try Table.ToColumns([#"13/03/2025 13:00:00"]) otherwise Table.ToColumns( #table({"Date"}, {{null}})))
      & (try Table.ToColumns([#"13/03/2025 13:25:00"]) otherwise Table.ToColumns( #table({"Date"}, {{null}})))
         & (try Table.ToColumns([#"13/02/2025 13:05:00"]) otherwise Table.ToColumns( #table({"Date"}, {{null}})))
)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"13/03/2025 12:00:00", "13/02/2025 12:45:00", "13/02/2025 12:55:00", "13/03/2025 13:00:00", "13/02/2025 13:05:00", "13/03/2025 13:25:00"}),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17", "Column18", "Column19", "Column20", "Column21", "Column22", "Column23", "Column24"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Expanded Custom1",{"Column4", "Column8", "Column12", "Column16", "Column20", "Column24"})
in
    #"Removed Columns2"

What I'm doing is the following:

• Grouping by the date columns you need
• Adding and index for each of the date/times
• Expanding the index for that group
• Regrouping based on the date and the index
• Making a pivot based on the date
• Adding a custom step to join all the columns of the dates
• Removing the index columns

Once again be carefull because with different dates this will mean that the combination of the steps are not automatic and you need to redo them.

See file attach.