Several CSV files as one Power BI data model

Which table is the main one

The file with the most rows becomes the fact table: the orders, the invoice lines, the bookings. The measures are built over it, and every other file is treated as a table that describes it: customers, products, regions. The model stage of the step-by-step route shows which table was chosen and why. When the data has a date column, a calendar table is generated and linked to it; further date columns are linked too, but inactive, because Power BI allows one active path.

How relationships are found

Every column of the main table is scored against every column of the others, and a relationship is made only when one candidate clearly wins:

• Two tables sharing exactly one column that looks like a key, such as customer_id, are joined on it.
• Several shared columns are refused, unless exactly one of them reads like a key. Joining on region or date would be a guess.
• The <Something> No. to No. convention of Dynamics exports is recognised. Two candidates, such as Sell-to Customer No. and Bill-to Customer No., stay ambiguous and are left for you.
• Text is never joined to a number, a key that repeats in its own table is refused, and the timestamp row version every NAV table carries is never taken for a key.
• A key of up to three columns, such as a document number with a line number, is joined through a hidden combined column on both sides.

Star or one flat table

If the lookup tables are joined, the model is a star: one fact table in the middle, the descriptive tables around it, and slicers on a customer or product attribute filter the measures. If you would rather have one wide table, join the files before you upload them; a single file becomes a single-table model with its calendar.

Step by step

1. Open dashgen.de and drop all files in at once.
2. Describe the report: which figures, broken down by what.
3. Press Generate Power BI, or, with an account, go step by step to see the model before the measures are written, and add a join where one is missing.
4. Open the project in Power BI Desktop and check the model view.

How each file's rows travel with the project, embedded or linked, is in the Excel to Power BI guide.

See also: A Power BI report from an Excel file · Power BI from an Excel file