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: idticket fecharegistro1 fecharegistro2 fecharegistro5 fecharegistro7 1219 26/11/2015 30...
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,

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

      • v-sihou-msft's avatar
        v-sihou-msft
        Microsoft Employee
        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,

  • 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.