Pivot unpivot Timestamp Values

[Anonymous]

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.

My data -

Date	Company	Value
3/13/2025 12:00:00 PM	Company1	0.2
3/13/2025 12:00:00 PM	Company1	0.2
3/13/2025 12:00:00 PM	Company1	0.2
2/13/2025 12:45:00 PM	Company1	0.5
2/13/2025 12:45:00 PM	Company1	0
2/13/2025 12:55:00 PM	Company1	0.5
2/13/2025 12:55:00 PM	Company2	0.5
3/13/2025 1:00:00 PM	Company1	0.5
3/13/2025 1:00:00 PM	Company1	0.5
2/13/2025 1:05:00 PM	Company1	0.2
2/13/2025 1:05:00 PM	Company1	0.2
3/13/2025 1:25:00 PM	Company2	0.5
3/13/2025 1:25:00 PM	Company2	0.5

Result I want -
if time = 12, 1, 2, 3 etc then

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

[v-mdharahman]

Hi @Anonymous,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are facing issue while transforming your data. It seems you want your time data to show only the hours and the minutes and seconds part should be taken as 0. As @MFelix already responded to your query, please go through his response and check if it solves your issue.

I would also take a moment to thank @MFelix, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[v-mdharahman]

Hi @Anonymous,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.

If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.
Thank you for your patience and look forward to hearing from you.

[v-mdharahman]

Hi @Anonymous,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.

Thank you.

[v-mdharahman]

Hi @Anonymous,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[MFelix]

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]

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]

@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 · Accepted solution]

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.

[MFelix]

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]

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]

Hi @Anonymous ,

For this I suggest to have a calculation and not a pivot of the columns.

Can you please let me know what is that OK/NOK will be based on? I can try and tell you how to use that calculation. It can be done either in DAX or Power Query.

[Anonymous]

@MFelix thank you for helping, So here, the condition I want in PBI Desktop is if Value for 12 pm =1 pm = 2pm then ok else not ok and for this condition the table has to be in converted in this format in PQ

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]

Hi @Anonymous ,

Just a quick follow up you have in the data 12PM but also 12:45 PM or 12:55 when this happens are you comparing 13:45 to 12:45 or everything is consider 12?

How do you handle that part of the data

[Anonymous]

@MFelix Raw data will have that data but we need to just filter only 12:00, 1:00, 2:00