Comparing data across multiple years with a lookup table?

[Alexicon]

I am still learning Power BI but something I am trying to do just doesn't seem to work well.

I work for a company that collects survey data and we want to compare multiple years of results to each other for presentation. We have all the data in an excel file each year, and in theory the questions on each are the same. They are also all text strings (as they are multiple choice questions).

What I want to do is futureproof the system by creating a lookup table that will in the future allow us to upload the new excel sheet (lets say 2019), and make a relationship in the viewer, and have that data ready to compare right away.

The issue is I can't get that to work properly. The data always seems to come out all skewed. Due to NDA I can't share what I have but if someone needs a more specific example I can mockup something with fake answers.

Keep in mind each excel sheet has over 1k answers, as it is several surveys.

Any advice? Tips? Should I just give up on the futureproofing entirely and just do this year by year?

Thanks.

[Alexicon]

In the interest of getting this issue solved, as it is beginning to make me pull my hair out.



This is the error I get when I try to refresh the data, but the data has the same table names and it *should* be the same formatting?



And here are the extra queries I get for no reason.

Any help would be wonderful.

[Aron_Moore]

I worked on a similar project. I needed to pull in excel books with increasing tab counts.

What I did was:

Query and get all the tab names (in your case you'd grab file names)

Create a function that loads excel data with tab name as the input

Use List.Generate to "loop" through the count of tabs calling my function passing in each tab name

Append all tabs/tables into one

You could try a similar approach. Good luck!

[Greg_Deckler]

I would use a Folder query and add a Year column in the query editor perhaps. This will Append everything together. Mock data would help tremendously.

[Alexicon]

So I tried the Folder query, and now no matter what I do I get the error 'Failed to save modifications to the server. Error returned: 'OLE DB or ODBNC error: [Expression.Error] The Key didn't match any rows in the table..'.' which is no help at all.

Google also has been no help at all. It's also creating a bunch of garbage queries when all of my data is set as tables? Basically it's a frustrating mess.

Which is a real shame as the Folder query seems to be *exactly* what I want. What am I doing wrong? What should I do to fix it or clean it up?

The simpler you explain it, the better all told. I am still new to Power Bi and I am trying to figure it out as I go. Thank you in advanced.