Inquiry: De-normalize data to create dimension tables: what are your best practice recommendations?

[DAXRichArd]

Software: Microsoft Power BI - Power Query Editor

Hello,

I'm looking for recommendations on best practice to de-normalize data in Power Query Editor.

• Desired outcome: create dimension tables to use in star schema data modeling.
• Desired outcome 2: use best practice that places least demand on computer resources (RAM, processor...).

Techniques I've used:

• 1   If the list of distinct attributes are few and I know the list won't change (deletions, additions),
  • I create a table and simply type the list.
    • HOME tab > Enter Data

• 2   If the list is long I
  • mouse left click the column
  • select Add as New Query
  • select Remove Duplicates
  • select Convert To Table

• 3   Technique found on Youtube Channel Pragmatic Works.
• https://www.youtube.com/watch?v=MrLnibFTtbA , starts at minute 00:41
• Instructor duplicates the fact table several times.
  • From each duplicate table, targets specific columns to use as dimension table attributes.
    • Deletes columns that won't be used.
    • Removes duplicates
    • Repeats process until all desired dimension tables created.

END TECHNIQUE DESCRIPTIONS

Closing

I am a novice at data modeling.

• I am realtively well versed at Power Query Editor using the menu features (tabs).
• However I do not know the M.Query functional language.

Please describe your techinque and it's advantages.

Thanks in advance for your mentoring and guidance.

DAXRichard

[Daryl-Lynch-Bzy]

Hi @DAXRichArd - they are all good approaches. Other options to consider are:

• Group By (Table.Group - PowerQuery M | Microsoft Learn)
• Put the table in an Excel file or Sharepoint instead of option 1