Forum Discussion

abelrmg's avatar
abelrmg
Helper II
9 years ago
Solved

Merge rows

Hi,

 

I have this data

 

How can i merge de rows and obtain one row with this structure:

idticketfecharegistro1fecharegistro2fecharegistro5fecharegistro7
121926/11/201530/11/201530/11/201511/04/2016

 

in according with the "estatus"

 

Regards

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    abelrmg

     

    You just need to create a custom column to append "Date" with estatus, then directly pivot on that column. See my sample below:

     

     

    Regards,

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    I would first consider why you want to do this.  You can get to that format with your Power BI visuals, but leaving the data unpivoted gives you the greatest flexibility.

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      9 years ago

      Agree with Anonymous

       

      Should you still want to go ahead, then it can be done with Power Query (M). The following code was created via standard menu options; the last step with advanced option "Don't Aggregate".

       

      let
    Source = Table1,
    #"Removed Columns" = Table.RemoveColumns(Source,{"idCliente", "idUsuarioRegistro"}),
    #"Added Prefix" = Table.TransformColumns(#"Removed Columns", {{"estatus", each "fecharegistro" & _, type text}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Prefix",{{"fechaRegistro", type date}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[estatus]), "estatus", "fechaRegistro")
in
    #"Pivoted Column"
      • abelrmg's avatar
        abelrmg
        Helper II
        9 years ago

        Hi MarcelBeug the query send this error

         

        Expression.Error: The column 'idCliente' of the table wasn't found.
        Details:
        idCliente

         

        Regards

    • abelrmg's avatar
      abelrmg
      Helper II
      9 years ago

      Hi Anonymous, i want to do this because i will calculate time between dates.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Still easy to do in the current format.  Create some sort of index marker and do your calculations.