Data Modeling from flat file

Power BI Data Model
1

Hi Everyone,

I’m new and still trying to get my arms around how to transition my large flat file that I receive into the fact and lookup tables that the videos speak to. It’s not intuitive for me. Is there a particular video or piece of advice or best practice you can direct me to?

Thanks in advance!

Greg

2

Hello @talk2gwhite,

Thank You for posting your query onto the Forum.

Well on our education portal we’ve a specific course designed which addresses this type of issue. You can go through the “Advanced Data Transformations & Modelling” course.

Below is the link of the course provided for the reference.

Hoping you’ll find this useful and meets your requirements. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

1 Like
3

Great! I’m in that course right now. :slight_smile:

4

Another quick way to see potential dimensions (if you’re using Excel) is to filter the sheet and look at the columns one-by-one to see if there is a small number of values …
Greg

5

@talk2gwhite,

In addition to the Data Modeling and Transformation Course, here’s another video on your requested topic that I really like. In 10 minutes start to finish, Patrick goes step-by-step from a flat file to a full data model:

I hope this is helpful.

– Brian

1 Like
6

Filtering in Excel generally gives me smaller groups of whatever is in the column, but I’m not getting what the next step should be. You mind sharing a little more? Newbie here.

7

I like these guys as well. Will definitely give it a try.

Thanks!

8

It’s great to know that you are making progress with your query @talk2gwhite. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

9

Thanks @BrianJ for sharing :slight_smile:
Also, picked up that a simple dates table can be set up using CALENDARAUTO

1 Like
10

Hi @talk2gwhite.

Sure, here’s a small sample.

eDNA Forum - Flat File to Data Model -1
eDNA Forum - Flat File to Data Model -1689×907 59.3 KB

Once you’ve done your Excel filtering to identify which columns might be dimensions, you can:

  • import flat file as a name you won’t mistake later, say [Raw Data]; deselect “Enable load”

  • create reference from [Raw Data] for [Customers]; delete all but “Customer” column, then remove duplicates

  • create reference from [Raw Data] for [Products]; delete all but “Product” column, then remove duplicates

  • create reference from [Raw Data] for [Status]; delete all but “Status” column, then remove duplicates

    eDNA Forum - Flat File to Data Model -2
    eDNA Forum - Flat File to Data Model -21960×1479 225 KB

  • create relationships: one-to-many for:

    • Customers[Customer] --> Data[Customer]
    • Products[Product] --> Data[Product]
    • Status[Status] --> Data[Status]

eDNA Forum - Flat File to Data Model -3
eDNA Forum - Flat File to Data Model -31737×969 56.9 KB

Hope this helps.
Greg
eDNA Forum - Flat File to Data Model.xlsx (9.0 KB)
eDNA Forum - Flat File to Data Model.pbix (26.3 KB)

2 Likes
11

Thanks! I just got to the Referencing section of the “Advanced Transformations and Data Modeling” course! So this is timely.