Hi everyone,
With the reference to Power BI Super Users Workshop >> Data Modeling >> Introduction to data modeling.
We have a lot of raw excel files and each file has excel sheets.
For e.g.
Excel file which has IT assessment tracking file and each sheet has Hardware, Computers, Software, Printers, Mobile etc.
Obviously, have raw information with no index files defined.
Q: Is it best to define index fields (lookup table) and clean it up (for e.g. wrong date formats, blank rows etc.) in Excel file FIRST and then import in Power BI desktop and assign relationships?
@sam.mckay
Hi Aroh,
It’s difficult to say exactly but this is my guess based on what you have described.
To me is seems that the IT tracking file could potentially just be one table. Do all the separate worksheets have exactly the same data structure, as in the columns names are exactly the same and the column details are the same?
If so then these should be appended into one table inside the query editor.
From there you should likely attempt to clean everything up inside the query editor. This is the most scalable way to do things. If you append the table first that means also you would only need to clean things up for one table rather than each individual one.
Here’s some information on appending - if you think this is the right thing to do.
Certainly check out all the content within this course as well. This is where I dive into all of my best practices around how to setup you data and data model effectively.
Chrs
Sam
Hi Sam
Please find attached RAW Excel file that I got from my IT team. RAW Sample from IT Team Sample - IT Assessment.xlsx. which has multiple separate worksheets where a few worksheets have similar structure (PCs, Servers, Warranty Start, Warrant End etc.) and few are not.
RAW Sample from IT Team Sample - IT Assessment.xlsx (47.2 KB)
I broke into split into different spreadsheets to have lookup columns (Country and Roles) and few fact tables as you mentioned in your videos in data modelling. Split into different spreadsheets.xlsx
Split into different spreadsheets.xlsx (54.3 KB)
Next, I imported the Excel file into Power BI Desktop and made the relationships as shown:
It’s a just a draft.
Any suggestions on the data model. I am also thinking on merging Computer fact table to Server fact table has it has similar columns.
Please advise.
So I have a quick look.
Yes It looks like you need to break some of these out because the data structure for some of these are different.
If they are the same though, it’s probably best to append them.
For you lookup table, you want to be looking for common dimensions that appear across multiple table. I honestly think you’re on the right track with your model.
It’s looks pretty good
