Achieve UNION SELECT query with and Excel file source

[juankypanky]

Hi to all the community,

I have been trying to make a special query with an Excel file source. This file comes from HR SAP system, and it can't be modified in its structure. This is an example of what I whant to do with the data: there is around 35 fields in columns that I need to convert to rows. I was working with Access with SQL and it has not a problem for me to convert a table like this

NAME - SURNAME - SKILL1Result - SKILL2Result - SKILL3Result - SKILL4Result ... 

Johnny - Nemonic -     5     -     7     -    8   ....  

in this other

NAME - SURNAME - SKILLS - RESULT

Johnny - Nemonic - Skill1 - 5

Johnny - Nemonic - Skill2 - 7

Johnny - Nemonic - Skill3 - 8

...

I was using UNION SELECT like this:

SELECT name, surname, "skill1" as skills, skill1Result as result

UNION SELECT name, surname, "skill2" as skills, skill2Result as result

etc..

Now, I want to design a dashboard in Power BI with the original Excel data (without Access conversions) and I have a lot of performance problems. I have been trying to make one query for each of the 35 skills, and after that Append all of them to one unique query. The result is good because I have the data in the structure that I need but, the performance is so bad, and the software goes slow and slow when I need to do changes or add a new other query.

I have no idea with other solution without changing the original structure of the Excel file.

Any advice from the experts?

Thanks in advance,

JC

[Anonymous · Accepted solution]

@juankypanky, 

You can use Unpivot transformation in Query Editor to avoid writting each query for each skill. The more queries you have in your query editor, the worse the performance is. 

[juankypanky]

@Anonymous Sound really good! I found an example in this address https://www.microsoft.com/en-us/microsoft-365/blog/2015/12/15/learn-how-to-unpivot-static-tables-in-excel-2016/ and maybe I can apply it!!

I'm going to try it right now.

thanks a lot!

JC