Help me get rid of a duplicate table

[bryanc78]

My data is from SharePoint and it's basically a test - see below.  The issue I'm having is that with each question in it's own column, I can't report on it unless I do one question at a time in a bar chart which is not what I need.  I need to show all questions in the same bar chart so I can see how they compare to each other.  So to fix it, I have to unpivot those columns.

I did that in my main table but doing that, messes with the other data, non-unpivoted columns.  For example, instead of 1 row per test, I now have 30 rows per test and it makes it look like the emploee took the test 30 times instead of once.  It makes it look like the tester gave out the test 30 times instead of once.  To fix that, I duplicated the main table and then unpivoted the 30 questions.  Now with all questions in one column and a quick relationship between the 2 tables, I can report on the questions just fine.

Now the new problem I'm having is the refresh times.

First report, with both tables - 1 hour refresh time

I created a 2nd report that just has the main table - 15 minute refresh time

I created a 3rd report that just has the 2nd, unpivoted table - 15 minute refresh time

I'm assuming that the report with the duplicate tables is taking so long as both tables are being updated at the same time from the same source.

How can I get rid of the duplicate table yet still be able to unpivot those 30 columns and not cause issues with the non-pivoted columns?

main table is about 2,000 rows and 50 columns

2nd, duplicate table, is about  56,000 rows and 18 columns

There are some columns I can remove like those stringid columns that SharePoint creates but I don't think deleting those will make much of a difference.

Any ideas?

[bryanc78]

So, using a reference table instead of a duplicate table works so much faster and all reporting is good

[v-chuncz-msft]

@bryanc78,

You may check if Reference queries in the Query Editor helps.