Power BI Tutorial | From Flat File To Data Model 

Patrick, this is such an important video. WAAAAYYY too many analysts at my company use the "flat file" method. When I was a newbie, that's what I was taught, so I never questioned it, despite the process being slow and inefficient. I remember people using VLOOKUPS in excel, of all things, to mash together enormous tables and them load them up into Tableau (lol). The idea was literally "if Excel crashes, just try again". So much wasted time. So many fire drills when handling urgent requests. Then one day, a colleague of mine told me about power query and PBI, and it felt like my eyes were opened for the first time. This type of stuff may be second nature to a DBA, programmer, etc... but a lot of us in finance, accounting, sales ops, etc. don't have any experience with creating data models, writing DAX or SQL, etc. We end up taking the long, arduous path to get to our destination, because it's the only path we know. Little do we know that we have a freaking jetpack that we can use to get us there in no time at all. Great video.

Thanks Patrick, your relaxed humor sets you apart from others and makes it easy to watch you videos.

Patrick, sometimes going back to basics is just great. Thanks for putting this together!

Hi Pat, I have been following this technique in modeling my data for long, applying it to some of the most complex datasets, and it has never let me down in terms of analysis. It might sound basic, but it isn't. Once you master it, any analysis, no matter how intricate it is, becomes a piece of cake. Thanks for revisting it.

Dude, THANK YOU! This is one of the best tutorials for real world power BI usage. So many tutorials don't include these basic steps, or simply brush over them. You're kind, explain everything in detail, and best of all it friggin works like a charm. I can't thank you enough for relieving my frustrations. You're the best

Hi Patrick, Love this. I'm new to Power BI and one of the projects I'm going to be creating a report for only has flat data. So this is going to be a total life saver!! (You get two exclamation marks as one just doesn't do it) Maurice

Beautifully explained. Love the content!

Patrick, you are always make me smile. Thanks!

CALENDARAUTO... why have you been hiding from me for 2 years? I think I often learn more from the lesser-focused topics than the main topics (sometimes). Thank you immensely!!!!!!!

this is so much better than the rest of power bi tutorials, great job!

So much easier to learn and follow - your teachings skills are brilliant - fell asleep on so many other videos

Get intimate with it Pat! 😂 man you’re great.

Thank you for this and all of your insightful videos - I'm learning so much!!! One huge request I have is, can you add videos with qualitative social science survey examples? A big problem with learning PowerBi is 1. newbies don't know the PowerBi terminology to know what we need to learn, and 2. All of the examples across all the PowerBi samples I've seen tend to focus on product, sales, revenue, etc. types of data. It would be great to see a qualitative-focused social science survey with 50-100 questions, including lots of skip values and various section topics in the survey, with data showing the same survey's results for different language-speaking populations in one country, and then for different language-speaking populations in another country, and then show modeling builds and reports of that data by respondent profile (country, spoken language, sex, age), survey section, question groupings within or across sections, and skip logic. Please can you guys tackle this for us social scientists? THANK YOU!

Loved your explanation ! easy to grasp and right to the point!!

On a date with my flat file. Very useful and entertaining, thank you! So basically, the flat file was separated into one fact table and two (three) dimension tables with linkages.

This just popped up in my feed two years later, but still a great tip for getting started in modeling.

I need the basics - I'm just starting out - Thanks for going back to the basics

i love your tutorials man. you are amazing.. respect

Thankyou for making it look so simple

Exactly what I needed, thank you.

These videos never miss!

First Time and I am loving it!!! Thanks!

This is the gold nugget. GOLD!!!!!

Patrick Great Post so clean the file and creation the table that will be used. Just have issue trying to set them up

Bro you are out of this world good. Literally my whole team is watching your videos now Cheers from BR

Perfect 👌 It's always important to go back to basics 👏

Cool stuff! I used to deduplicate dimension tables in this way, by selecting all columns and then using remove duplicates. That has the risk that if the csv file is made up from different sources, the key might be duplicated with different definitions. My new approach is to sort the dimension table by modified date descending (if there is such a column) and then use remove duplicates only for the primary key column. If there are duplicate keys then this approach appears to retain the most recent definition due to the sort by date.

Great set of videos Patrick!

Great video as always Patrick, good run through on the basics of creating a data model from scratch

Back to basics is always good.

Interesting, Thank you for sharing Patrick

Another good video, thanks Patrick

Dude you're brilliant

So useful. Thanks 🙌

Great tutorial - it's really helped me out. Also Texas represent!

I wish I knew this stuff well enough so I could be an honorary guy in a cube. However, with each video I am learning more and more and getting closer to this goal.

Thanks Pat. I just subscribed today.

Love it and great explanation 😍

Great video thanks for doing this

Great video!

“Out in the wild” 😂😂😂 I love it!

Thanks. This helps a lot 👍🏼

So excited about bi

Great work Patrick

Great stuff!

Excellent!

Amazing content!

Great video. Although I'm a bit stumbled on what to do if I don't have keys in the original file. Is there a way to add them?

thanks Patrick- another great video :)

You can also remove duplicates by clicking on the option in top left corner icon of the table in query editor. Saves time ;)

Thanks for sharing.

I was wondering if I don't have the Productkey or Orderkey in the main datasheet. How we can make the relationship with the fact table after creating the different Product and Orders table.

thank you for wonderful video

Hey! Loved this video, do you have a data transformation video on excel speadsheets in a given scenario where a business might be still using excel as their database, you know, the common top 4 rows is a header, they use columns for Quarters and use aa single column as a category for different targets and a single column for target value? Keen to know if you have video to manage transformation to designa report out of this manual data entered spreadsheet.

Thanks for the explanation, few doubts I have 1. If the data doest have keys or codes how do you add. 2. If the file is dynamic how the above model works when there are new columns in next updated .csv file

Hey i really liked your videos wonder if you can create a video on azure cost management usage details.

Thank you Patrick. I am Power bi newbie and wondering if you have videos on org change impacts reporting in PowerBi?

Hey Patrick !! BTW its nice VDO .. But if the flat file data is fully transactional where we store only keys for the respective dimensions then I don't think so we can create a model having Dimension tables (Key,Value pair) .. Any idea how to achieve this ? Thanks

Patric! I did something similar recently but rather than duplicating the data set multiple times (which results in multiple queries, I did some basic cleanup and then created a bunch of reference queries for modeling. Is there a reason you duplicated rather than referenced ?

Awesome tip to duplicate to create other supporting tables...easier to get to STAR model!

Patrick, great job! Awesome. What happens if I need to update the datas after aplying all these changes using the original file? Is it possible? Is it going to give a error message? Thank you. Is Power BI able to identify the changes?

great example. could you also provide the data you are working with?

because power query is key sensitive I think you should add an upper or lower case for product names; in case you add new data ( preventing relationship)

Great video Patrick! Where does one get those cool looking PBI Shades? ;)

Hello, Thank you for a wonderful tutorial. I wanted to get more specific though in terms of what the benefits are of transforming a flat file into a star schema. Is it simply organizational in nature? Does it make the file size smaller? Do filters run more efficiently? What are the specific sort of benefits from transforming a flat file into a star schema? Thanks

Thanks

O boy you nailed it. Good job. May I know the name of Microphone you are using to record?

@Patrick, how do you create a unique product key or territory key for a flat file that does not come with one and you need to make one to create lookup (dimension tables)?

Duplicate or reference? When you get a new file if you duplicate will it automatically update the 3 new tables in either option (example sales data in September then sales data in October)? Asking since you might have new products in October and new sales territories etc...Thanks! Videos are great!

So to link the pieces of data from one data table to the other, you just have to have at least one column in both with the same data to link the two together? I'm new to PowerBI and data table relationships :)

Great Video . Thanks. After you publish this on PowerBI.com how do you handle updates to the data? Also would this work well if the flag file is very large (many millions of rows). Thanks,

Nice video, This is BI = Basic Intelligence for anyone.

Q: Thanks Patrick. Fab. If IT send you 10 sales excel for 10 departments, different format, how do you decide if we clean them up and combine into one table, or just keep them separated?

Great presentation my friend!

Golden!

Great video! So, which table would you use for calculations and create new columns? I mean, the fact table? Do you even need it? Thx for your input!

Great video! Once I upload the model to PowerBI Services, where should I add new records to the data set at that point? Only on the cloud data set? Do I even need the local spreadsheet any more at that point? Not the one Power BI desktop, but the first local copy I started with. Any feedback is appreciated. Thanks

7:53- shift+enter to start a new line in the formula bar.

Thank you Patrick, this is awesome! I've been trying this yesterday with a Flatfile of 160MB and a second one with 50MB. When i either "Duplicate" or "relate" the Queries to the flatfile and build my dimensions, it seems that for "refreshing" its taking a loooong time, as it loads every flatfile as many times as i have created duplicates to build a dimension. Is there anything i can do about this? Am I going wrong at some place or is this normal behavior? Thank you :)

Patrick, looking for help. I need to add a new column and data in my data set in excel, that will be uploaded into an existing PBI model. What would be the best way to accomplish?

Q hey Patrick is there any way to update the dimension table if new data is updated in the original flat/excel file.For eg we have 10 products in the original file but now new 2 rows products are added in original file

Sir amazing video.. Please share dataset

Hi Patrick Does this methodology apply on Direct Query table instead of just flat CSVs? Working on an extremely complicated report, I've prepared a data table (flatten out/deformalized) in SQL Server by getting all the required columns for the report from different sources. I've ended up with some 180 columns and around 20 million rows. The PBI report will eventually be published to the Service (premium). What do you reckon, applying the cleanup method using different queries (as in this video), will help? Any insight is appreciated.

Grate video

Nice

Hi! I know this is an old video but I am wondering... Is it better now doing all the data transformation using Data Flow instead of doing on PBI Desktop?

Hi sir. It's a great video! Just have a question that was asked to me. If you already have all the info that you need in the flat file, why do you have to split them up in different tables?

After building a Data Model from this flat file,What if on any other day v have data with same column name but different data...do v need to build Data Model again from Scratch or Power Bi fetch it...Pl Clarify me on it

I was doing this until, i wanted to create a time series data model with ability to filter data by date, week, hour. if we do this approach we can definitely do that however when you want to create hierarchy for for date till hour level it gets tricky

Nice video, good example tells how to create your own data model step by step

Hi Patrick, great tip! But I do it a little bit different ;) - Create a Sales_Raw query that is marked to not load - Reference the Sales_Raw for every other queries (ex: Product), remove the columns, remove duplicates Thanks

Do you keep keys as whole numbers or you change the data type to text?

Hey Brother you make wonder videos on power bI and help millions of people out there .Its a Request please share the dataset to practice along, it would be great

I want to create dimension and fact table to have star model. . I have 2files. Both files have some common columns and similar data structure . Hence I am using Full Outer join , File one - 2085 recrods , file 2- 4981 records. What is the best join option i should be using ? Both files have some similar records. IS there a video/link which tells how to create dimension using merge? any help wll be highly appreciated

I haven't watched this particular video but I was looking to remove an excel source connection from the exisistng model, there wasn't any way to do that. Once you connect excel to a sql data model , there is no way out. You can remove it from the model per se , but the connection still exist which causes dataset refreshing issues in the server. I was connecting a report to this data source with sql and excel source in the server. the refresh failed , so I thought I will remove this connection but it will not let me. If you find a solution for that please let me know.

Love your videos! :) However, I'm facing a challenge as my source is not a flat file but a folder with several csv (one with 300k rows, the rest around 30k each and everyday there is a new file). My problems are basically two: 1) There are duplicates among the files which I need to clean, and 2) The file is really big. I'm starting to divide that master file into different dimensions and a fact table, but as I need to merge it first and get rid of the duplicates, it's also taking ages to update. Any tip? Regards from Barcelona, Spain! Victor.

Can we create page number indexing in SSRS?

Hey Patrick its a great video. I didn't get the last part where you said separate the Data Model and Data Viz.

Please provide a link to the csv file so we can follow your steps. Cheers!

After creating a date table, did you really need to manually set up a relationship between it and the other tables? I thought that's what "mark as date table" did automatically.