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,