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
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,
Solved! Go to Solution.
@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!