Forum Discussion

Anonymous's avatar
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.

 

My data -

DateCompanyValue
3/13/2025 12:00:00 PMCompany10.2
3/13/2025 12:00:00 PMCompany10.2
3/13/2025 12:00:00 PMCompany10.2
2/13/2025 12:45:00 PMCompany10.5
2/13/2025 12:45:00 PMCompany10
2/13/2025 12:55:00 PMCompany10.5
2/13/2025 12:55:00 PMCompany20.5
3/13/2025 1:00:00 PMCompany10.5
3/13/2025 1:00:00 PMCompany10.5
2/13/2025 1:05:00 PMCompany10.2
2/13/2025 1:05:00 PMCompany10.2
3/13/2025 1:25:00 PMCompany20.5
3/13/2025 1:25:00 PMCompany20.5

 

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

DateCompanyValueDateCompanyValue
3/13/2025 12:00:00 PMCompany10.23/13/2025 1:00:00 PMCompany10.5
3/13/2025 12:00:00 PMCompany10.23/13/2025 1:00:00 PMCompany21.5
Need Help
  • MFelix's avatar
    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.

14 Replies

  • MFelix's avatar
    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's avatar
      Anonymous
      Not applicable
      1 year ago

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

      DateCompanyValueDateCompanyValue
      3/13/2025 12:00:00 PMCompany10.23/13/2025 1:00:00 PMCompany10.5
      3/13/2025 12:00:00 PMCompany10.23/13/2025 1:00:00 PMCompany21.5
      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

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

        DateCompanyValueDateCompanyValue
        3/13/2025 12:00:00 PMCompany10.23/13/2025 1:00:00 PMCompany10.5
        3/13/2025 12:00:00 PMCompany10.23/13/2025 1:00:00 PMCompany21.5
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.