Forum Discussion
How to normalize a table in Power BI
We would need more details specifically of the data and information those tables contain but it would need to be done in Power Query. The techniques will vary on the quality of the data but essentially you would be duplicating/referencing queries, removing columns, removing duplicate rows, etc.
Like with any other ETL tool there is no free lunch. The price must be paid either by you (the developer) or by them (the end users). General guidance is that you have to pay, so that the users have an enjoyable UX.
Thanks. I appreciate your point about the necessary effort on the developer's part to ensure a good user experience. However, I'm still seeking specific guidance on the initial question regarding the normalization of tables in Power BI to the 3rd normal form. Could you provide more detailed advice or steps on how to effectively normalize multiple Excel tables within Power BI, particularly considering the challenges of data duplication and model size increase as new data is appended weekly? Any specific techniques or best practices in Power Query for handling this type of data transformation would be greatly appreciated. I appreciate ray_aramburo mentioned data duplication would be involved, removing columns and rows etc. If this were to be done, would the model size not then affect query performance?. Or it would this have negligble effect?.
There's a balance of normalization form and business scenario. The extremes would be one giant flat table vs 6NF. You will have to find your own balance. 3NF sounds reasonable.
The more you normalize the lower the model's storage requirements. You have to pay the price for that in Cartesian currency later.
Normalize until your model is usable, but not more.