a quick one to add, for Excel users
Need to start to think in a database structure like style. That mean divide the elementary data into different tables:
- Fact : hold the numeric values on which you will perform the analysis/aggregation (sum, count, etc…); and
- Dimension : are the attributes used for cut and slice the analysis performed on the Facts.
In excel when you have a fact in a table and want to add extra attributes (dimensions) for your pivot tables/analysis, will start using lookups (vlookup, hlookup, match/index) to import the required attribute to end up with a flat table.
Well approaching the database structure, but bear in mind the structure in Power BI may be simpler than a database one, you have your fact table that are refereeing to the dimension table by a link.
Link from the dimension to the fact table where one occurrence of the attribute is in the dimension table and may be multiple in the fact.
To make the visualization of the concept easier, when structuring your database model put the dimension on top and the fact below.
Facts can be cross sliced if the table is linked to the relevant dimension.
In the databases theory the reference between the dimension and fact table for each dimension is a dimension ID that better to be numeric format (integer).