Hello,
I am nearly complete with the Power Query course. I am trying to apply the knowledge I've learned to my real spreadsheet I'm working on at the office. My problem still is trying to find the best way to separate my tables so that I can bring them into power query as relationships. I've done both merge and append and it goes back to how I've organized my data. Something isn't working. For sensitivity reasons I can't share the file, but here is how it is arranged:
- I have 5 fiscal years of financial data that is currently organized as a single table using the following columns left to right: Fiscal Year, Month, Program, Program ID, Nomenclature, Labor Control, Labor Execution, FTE, Onboard
- Based on relational database rules, I do not have a unique identifier, so I created a field that is unique to each entry and called it "UID". Each fiscal year and month is approx 42 rows so I have about 2500 rows of data for the 5 yrs.
- Should I create a table for each year? The ultimate goal would be to bring in non-labor data too, same type of set up with the first 5 columns the exact same as above but the last 4 columns have different names and data
- I'm having a terrible time collectively gathering these tables and finding relationships in power query. My ultimate goal is to create an interactive dashboard for my boss where he can click on labor and non-labor information updated monthly by me.
Thank you very much!
Erica N.
Hi Erica,
I presume you mean Power Pivot, not Power Query, as it's Power Pivot where you create the relationships. Power Query is simply the getting and cleaning data tool. I had a look at your course progress and it shows that you haven't started the Power Pivot course yet, so maybe that's why you're struggling a bit.
It sounds like your data is in the perfect tabular format. You shouldn't be creating separate tables for each year. Leave it as is. You don't need a unique identifier for each entry. I'd remove this column as it will reduce Power Pivot's ability to compress your data.
I recommend finishing the courses before you try to implement the techniques in your own work. That way you'll have the full picture of how these tools work and it will be easier to relate it to your scenario.
When it comes time to create the relationships, your dimension tables will be dictated by the fields you want to analyse the data by. e.g. if you want to analyse the labour and non-labour data by Program ID, then you'd have a dimension table containing a list of unique Program IDs, and you'd relate the labour and non-labour tables to the Program ID table.
I hope that clarifies things, but please come back if you have further questions.
Mynda
