Forum Discussion

DEngel's avatar
DEngel
New Member
1 year ago
Solved

Table Transformation - Column Names into Repeated rows

BI Experts,

 

I am attempting to perform a batch import of excel files from a folder where the native data structure for one table is:

 

Service NameSales Person ASales Person XSales Person Y
Service A102030
Service B405060
Service C708090

 

Where what I would like to do is transform this into a 3 column matrix such as for the first example file:

Service NameSales Person NameRevenue
Service ASales Person A10
Service ASales Person X20
Service ASales Person Y30
Service BSales Person A40
Service BSales Person X50
Service BSales Person Y60
Service CSales Person A70
Service CSales Person X80
Service CSales Person Y90

 

Unfortunately, I am running into a few challenges:

1. A way to re-structure this data for a singular file (I.E. importing just a singular file - outside of a brute force approach)

2. A way to do so that is tolerant to each file having a different set of initial values for the column names

3. A way that allows for "X" number of services, and "Y" number of sales people

 

I hope there is an elegant DAX method that can perform this type of transformation simply.

 

Thanks in Advance,

David

Need Help
  • collinq's avatar
    collinq
    1 year ago

    Hey DEngel ,

     

    I think that if you bring in the data as shown in the first image and then do a transpose or pivot on the columns after the first one you should be able to get the result that you want.

     

    I took this:

    I selected the first column and unpivoted the other ones:

    and got this

     

     

2 Replies

  • collinq's avatar
    collinq
    Super User
    1 year ago

    Hey DEngel ,

     

    I think that if you bring in the data as shown in the first image and then do a transpose or pivot on the columns after the first one you should be able to get the result that you want.

     

    I took this:

    I selected the first column and unpivoted the other ones:

    and got this