Building a star schema from a flat export, in three moves
IT sends you an export of 1,200 order lines from the order system. Your first reflex is to write a measure right away. I understand that, but officially it should only be your third reflex.
Every line repeats the customer name, city, region, product name and category, eleven columns in all. Not all of them are needed. For the quarterly meeting your manager wanted revenue by region and by category, right? The export is open and nothing is built yet.
The three moves, in this order
You turn the flat table into a star schema, the order lines in the middle and everything else around them.
Move one is fixing the grain. The grain of a table is what one row stands for. In this export it is one product on one order. I always start there, because every later choice hangs on it.
Move two happens in Power Query, the layer where you load and prepare your data. Everything about the customer gets its own table without duplicate rows, and so does everything about the product. Customer number and product code stay in the order lines, because the links back to the order lines run along them. That saves a lot of data and so storage. Microsoft writes that an export or data extract is usually flat, and that Power Query is the place to split such a source into several tables.
Move three is the measure, the calculation that only runs when someone looks at the report. Finally.
What is left after move two
Customers now has 85 rows, one per customer, with name, city and region. Products has 60 rows in 6 categories. Order lines keeps 1,200 rows, with six of the eleven columns: order number, order date, customer number, product code, quantity and amount.
The measure is one line:
Omzet = SUM ( Orderregels[Bedrag] )
SUM adds up the amounts within the filters of that moment. Put region in a matrix, the cross-table in your report, and the filter runs through the relationship on customer number to the order lines. Your measure never mentions region. That works because the customer table filters and groups, and the order lines add up. The same measure also gives revenue by category, without any change.
Then the shipping costs arrive
After an update to your ERP there is € 6.95 shipping per order, and in the export that amount comes back on every line of the order. Your manager now wants the quarter total, shipping included. With a measure straight on the export, SUM counts 1,200 times € 6.95, so € 8,340. But there were 400 orders. An order of three lines counts three times.
The grain question from move one would have caught this. That amount belongs to a whole order, and the grain here is an order line. So you know you have to repeat move two, with a table of its own and one row per order. That table has 400 rows, and the same SUM over shipping costs neatly gives € 2,780 there.
Revenue by region was correct on the flat table as well, by the way. The difference sits in columns that describe something other than one row. You only see that once you have named the grain.
The question before your first measure
What is one row in this export, and which columns describe something other than that row? If a column does not fit the grain, it gets its own table in Power Query (or with SQL in your data warehouse).
What to do tomorrow
Sort your own export on order number and look for an amount that comes back on every line of an order. That is your shipping column. Which one was it in yours? Write down in one sentence what a row in your export stands for, and see which columns do not belong to it. Would you like to go through your own export with these three moves together? I am happy to think it through with you, see trainerbjorn.nl.