Forum Discussion

Anonymous

Not applicable

1 year ago

Solved

Pivot unpivot Timestamp Values

Hi everyone, Would appreaciate help with this. I want timestamp values of a column in a separate column as per the conditions, I am trying to do this in PQ but unable to achieve exact result....

Need Help

MFelix

1 year ago

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.

Pivot Column.pbix30 KB

MFelix

Super User

1 year ago

Hi Anonymous ,

On Power Query do a split between the date and the time

Select the date column and do a new column with date only

Select the date column and transform the column to only date

Now you can use both column on your visualizations has you prefer

Anonymous

Not applicable

1 year ago

Thanks MFelix
But how do I make the result look like this:

Date	Company	Value	Date	Company	Value
3/13/2025 12:00:00 PM	Company1	0.2	3/13/2025 1:00:00 PM	Company1	0.5
3/13/2025 12:00:00 PM	Company1	0.2	3/13/2025 1:00:00 PM	Company2	1.5

Anonymous

Not applicable

1 year ago

MFelix
Can you please help with how do I get the tables looking like this :

Date	Company	Value	Date	Company	Value
3/13/2025 12:00:00 PM	Company1	0.2	3/13/2025 1:00:00 PM	Company1	0.5
3/13/2025 12:00:00 PM	Company1	0.2	3/13/2025 1:00:00 PM	Company2	1.5

MFelix
Super User
1 year ago
Hi Anonymous ,

What is the purpose of having the information in PQ like this? If you place the information like this it will be much less flexible and on refresh you will have problems because of the formatting of the new names/ columns.

Do you want to present the data like this on the report?
- Anonymous
  Not applicable
  1 year ago
  Hi MFelix yes, and the dates will have different dates, time will have only 12,1,2,3,4 etc basically hour difference and not each minute difference. The reason to do this in separate columns is because I need to make a calculated column in Power BI Desktop and check if the "Value" for 12,1,2,3,4 is same then 'ok' else 'not ok' I should be able to filter through the dates.

MFelix

Super User

1 year ago

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.

Pivot Column.pbix30 KB

Forum Discussion

Pivot unpivot Timestamp Values

Related content

Pivot / Unpivot?

Unpivot Column

Unpivot Question

Unpivot

datetime2 shows timestamp