sqlexcelpowerbi

Convert a value column into multiple columns in Power BI per customer

I would like to know how to convert the following table

Customer Score
ABC 25
ABC null
DEG 1
XYZ 10
XYZ 7
XYZ 0
XYZ 26

into this:

Customer Score1 Score2 Score3 Score4
ABC 25 null
DEG 1
XYZ 10 7 0 26

Please share your answers within the context of SQL, Power BI, or Excel to solve this problem.

Solution

  • You could generate a row number in your SQL as a new column and then pivot on that in PowerBI or Excel (putting rownumber in the columns, customer in the rows, and score in your values).

    Syntax for generating a row number in most RDBMS would be:

     SELECT Customer, 
     Score, 
     ROW_NUMBER() OVER (PARTITION BY CUSTOMER ORDER BY 1) as rownumber 
 FROM yourtable

    That would need to be tweaked for AccessDB or older versions of MySQL and whatnot.