Table Transformation - Column Names into Repeated rows

[DEngel]

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 Name	Sales Person A	Sales Person X	Sales Person Y
Service A	10	20	30
Service B	40	50	60
Service C	70	80	90

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

Service Name	Sales Person Name	Revenue
Service A	Sales Person A	10
Service A	Sales Person X	20
Service A	Sales Person Y	30
Service B	Sales Person A	40
Service B	Sales Person X	50
Service B	Sales Person Y	60
Service C	Sales Person A	70
Service C	Sales Person X	80
Service C	Sales Person Y	90

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

[collinq · Accepted solution]

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

[DEngel]

Thanks a ton Collin, that worked perfectly to be able to manage one file.

I am working through a couple of issues with file structures varying (between each) and having to manage to use techniques like that stated here: Solved: Replacing a specific value in ALL cells, without h... - Microsoft Fabric Community

Unsure if you know of a similar approach for replacing errors for all columns.