cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
juankypanky Frequent Visitor
Frequent Visitor

Achieve UNION SELECT query with and Excel file source

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

1 ACCEPTED SOLUTION

Accepted Solutions
NickNg278 Senior Member
Senior Member

Re: Achieve UNION SELECT query with and Excel file source

@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. 

2 REPLIES 2
NickNg278 Senior Member
Senior Member

Re: Achieve UNION SELECT query with and Excel file source

@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 Frequent Visitor
Frequent Visitor

Re: Achieve UNION SELECT query with and Excel file source

@NickNg278 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-... and maybe I can apply it!!

 

I'm going to try it right now.

thanks a lot!

JC