Note that doing this approach could result in the underlying table being queried multiple times on your data source to satisfy the multiple Power Query queries.

Rather than creating a new item "Not Supplied", I use "" or "". That way those items appear first in an ascending sorted list or a slicer. Plus they stand out when looking at many rows in a table. Now that it is clear they exist in my data, I can take steps to address them.

Dude I can't tell you how much your videos have helped me. I inherited a mess of a database in my new position and had no one to really learn from. You rock at teaching

so thank you for this video. there was not too much talking, but only the amount to provide support for the steps. i love how you broke the task down into easy to follow steps and explaining why it was done that way. 🙂

Thanks man, one hour looking for this

What I like is how the star schema changes how you look at you data. It organizes your thought. Segments your perspective.

Dude, you just saved my life! I was looking for it and all the results I got were like "how to replace ID with the name", and I wanted the opposite! You just got a subscriber from Brazil! Great content! Cheers!

Thanks for confirming I wasn't that nuts when doing exactly this. The merge step can take a LONG time for bigger tables though. Nice video!

Excellent video! Exactly what I needed with no unnecessary filler. As a budding data engineer, this was a huge help! You are both a scholar and a gentleman! :-D

This was so helpful! Now I’m trying to add more columns from my two fact tables to the new tables 😅 without my PK’s yet.

I’m just learning Power BI and your videos are so helpful and fun to watch! Thank you so much!

I have been searching for you. Great video🥰🥰 thanks a lot

Thank you for giving good information

Your instructions are certainly stepping stones towards becoming "a big deal", keep 'em comin'

Thanks ! Excellent advice at 6:45 !

Thanks for this, now I know how to narrow my fact tables down

I can’t thank you enough for this video! I think this will solve our problem at work! 🙌🏻🙌🏻

Thanks dude. I didn't know about this method which doesn't use the "duplicate function". Much easier!

Awesome!! I am going to try this method! Thank you for walking through it.

Hi Patrick! I had to create IDs and I did a very similar process but instead of 'right click -> add as a new query', I've duplicated the entire table ('right click on main table-> duplicate') and from them I've performed exact same steps that you, what is the difference? Thanks for creating such great videos!

Really useful tutorial for messy data. Thanks!

Thanks, I actually used this yesterday and your steps worked like a champ, YA HIGH FIVE! Thanks for explaining what trim does too, that tool is very helpful. Going to put my gloves back on, clean clean clean data haha good thing I have a janitorial degree from the Corp. haha

Brilliant way to generate look up tables. Thank you

You don't even imagine how I learn looking your videos, thousands thanks for your great job

Thanx Patrick, when comparing this method with using Dax combine values is there a performance difference?

Very dope video. It really helped me

This video helped me a lot thanks was getting low percentages merging tables

Good Stuff Patrick!!

Just what I needed. Thank you

Hey Patrick, your videos are just awesome. Thank you so much for such easy to understand and accurate explanations ! Great Job

I do this also, but instead of merge I do a transform with my buffered table. So if I have multiple columns, it's one step. I usually do TblID[ID]{List.PositionOf(TblID[Element], [ThingToReplace]} I don't know if the merges would be faster

Patrick, very cool video.. Normaly you receive multiple tables and have to do something with it, now you give an example if you receive one big (=wide) table. Thank you for this interesting example.

thanks this helped a lot!

Nice video. I like these quick an useful data wrangling type videos. Please keep them up.

This is beautiful

Again, I went back here to refreshen up the ideas you got here Patrick. It really helped me a lot with my stuff! How about using this method in Import mode of connection then the data is updated, Does the other table will also be updated together with your other keys? Thanks a lot man!

This was perfect. Thank you!!

This is great! Thank you so much! You guys make this fun to learn. Keep up the good work!

Yep. All tallies with what I do ! Thanks for confirming !

Thank you so much ! This helped me a lot.

This video helped me a LOT, thank you so much

How would this work with multiple columns. Columns example: cost center, cost center mapping and period.

Thanks for the video. It was really helpful.

Is having the relation on ID giving a better performance than just having it on airlinename in the airline helper table? Is the performance gain of this worth the performance overheat you mention for generating the keys? I would (until I saw this video) just have made the link on airline name....

This is incredibly helpful. Thank you so much!

Thanks, Patrick. Good stuff as always!

Hi Patrick , I love your explanation very much , actually im beginner , pls help me below , i want to lookup one particular product in another table , but that product was booked by two different customer , finally it was sold to one customer , how to create a relationship for this from one table to another table

The most beautiful part is that it makes that column disappear from original table!

That is seriously good stuff! I've been thinking about something similar and now I have the ultimate solution to make this work. My only more-burning question at the moment is how do I get one of those Power BI coffee mugs... lusting after that!

Yooow! Hi Patrick. You asked if I would make this in a diferant way. Yes I would. Until 5:21 I make just like you. But after, I wold add a custom step as "Table.Buffer()", and I wouldn't "add as new query". I wold make a Reference, rename this new query as "Arlines", remove other coluns, and make the same you do until 6:41. Ok, ok calm down you are thinkin "But Daniel, this would make a 'A cyclic reference ' and this won't work". So, to resolve this I make a new Reference to the 1st query, rename to "fData" (or something) and then I Merge Query with "Arlines". To end I would hide my 1st table from my data model. So let me justify, I wold do all this work because if I find out that I forgot to make a repalce, I would just need to make this new step in one Query. The way you did if my file change just a little bit, I would need to change twice. You think that this would gain or loss process time? (sorry possible spelling errors, I don't speak english very well)

Great one Patrick..I'd add one more step to yours and hide the airline id from the transactions table

Love every your video, huge help to me. Thank you so much!

I use this method all the time, works very well! Cool to see you guys use the same methods! What's the best practice for troubleshooting the data once you've broken everything out? For example, if you need to sift through that fact table by airline name, it become rather tedious to go back and forth between the tables matching keys. Worse yet, if you have multiple dimensions that are filtering the fact table, it can be difficult to identify the proper keys to look through the fact table. If the source is a relational database, this could be done in the database, but in this situation, the source is a CSV or other file, so that type of out-of-Power BI querying is not possible. Thanks!

Great video, Patrick!

Cool T-Shirt Patrick !!

Great video, thanks for all the help

Came looking for exactly this. Great stuff!!

Great job, Patrick. This is helpful. I am going to use this technique in my dataflows so that it doesn't slow down the refresh. My question is about CamelCase. I heard (from the Tabular Editor Best Practices Analyzer) that CamelCase is not best practice. Why do people say that and what do you think?

Maybe a newbie question, but still. I come from SAP BW world. How to ensure a new index will be automatically created and a new entry will be automatically added into this Airline dimension table when a new unique Airline name appears in source data (excel, csv, table,...).?

You must be a mind reading Jedi. I needed this as I am currently doing a similar approach through much more convoluted methods of duplicating tables and removing columns to get down to a basic ID Table. Your method will save me much time and my mind and emotional state are very appreciative. Have a question of how I can possibly have my company's IT department give me access to view the relationships they have built and had created through our data warehouse. Currently, they have many many tables with similar or exact names of different columns and attributes. It's making me go through a process of trying out different combinations figure out where they have pulled the data from and what relationships they have built between the two.I relate it to shooting a target with an arrow, in the dark, blindfolded, and with one arm tied behind my back. I'm not lazy, I just want to be efficient and not waste my time with the guessing game approach I find myself in.

How do you add the ID back into the fact table if you want to avoid (merging for query load time reasons)?

First of all, mapping tables are cool:) When your end user is someone who knows how to use PBI, then this method may come in handy to clean up the main table, however most people on the end of the chain possibly just know the frontend and only things they will change will be the filters. Therefore there is no need to create an artificial (in this particular example) mapping table. Nevertheless great video, I really enjoy your content

Very helpful - thank you!

Best explanation ever.

Hello All, Is there a method of automating the process of Merging two tabular model ? I am using manual method in BISM normalizer

hey Patrick... would about joining on alphanumeric keys with Tpye as "any" ?

Will a relationship between two integers in PowerBI perform faster than a relationship between two strings? In SQL I would say yes, but for Power BI - I don't know.

Can you still use this method if the incoming values for Airline are constantly changing? (e.g., new airlines are regularly being added to your original table)

Great video! If you get additional data, let’s say, with a new airline, will the refresh process take care of everything? Meaning add the ID to your airline table?

thank u

This is a beautiful and relevant video Patrick. I've often found myself thinking about this business case where the dimension data is long text strings and doing joins on such dimensions is fraught with uncertainty at the best of times. There is one use case I find myself thinking about, which the video does not address so I'm pinging to understand how you have thought about this. Imagine the flat file had different values for "TWA", "Transworld Airlines", "Trans World Airlines". This technique would create a different custom key for each of these entries - in reality however, these should point to the same key. Therefore, using this technique in power query will not cover this particular use case. Up in my head, the only way to do this is through manual intervention where the key is inserted through a manual scan of the table to ensure that "TWA", "Transworld Airlines" and "Trans World Airlines" all point to the same key. Short question - is there a way to reject this "lazy" technique and become more "efficient"??!!!?!

Nice video, do we have other methods to remove many to many in power bi?

Hi Patrick, thanks for the video. I have one question: Why we don't use AirlineName directly as the "Key"? We can skip the step of merge and it should be faster. Isn't it? Or I miss anything?

I love this t shirt

The problem id that with any big dataset this method will make the data refresh terribly slow.

Cool video, thanks! Do you have a video about caveats of joining on strings? Tnx!

Thanks in my real world with no clean data warehouse the data modelling and joining is the biggest hurdle to using power bi

Espectacular!! Muchas gracias!

Great video, I do this when I want to split up a column that has multiple values, such as a tags column that would have a list of tags delimited by semi-colon. That way the user can select a single tag and see all matching rows that have that tag. Question: Why duplicate instead of reference if you are doing multiple columns?

I wish that I had watched this video last week! I did this in a much more manual way.

Hey Patrick, great video! Do the Airline query update the names when new ones is added in your ERP system?

Great video! thanks!

Excelente, Just saved me hours of Work!!

I really liked the way of subtly taking performance into consideration (Look for int), is there a visual form of execution plan? What would be the equivalent of SQL Execution Plan to use with Power BI? Exists?

Thanks alot

Patrick, I am using your solution but I am facing performance problems when I join (inner join) both tables by the text key column (number of rows 1.000.000 aprox). Thanks

Thank you so much for the video. How to connect Dynamic folder(File name changes day to day) ? Extracting the data from new file through refresh is getting failed?

Is there a way to automate this , i have 40 tables I need to move from the flat-file

How does this affect performance?

Great video Patrick. Such a clean way to create lookup table and join. I have 2 related questions. 1. If I need to join 2 tables on multiple columns (e.g. composite keys), do I create a lookup table with those columns from the 1 side of the 1:N relationship? 2. If I need to join on BETWEEN clause, e.g. table1.date between table2.startdt and table2.enddt, what would be the best approach? Thanks in advance.

Hi, Patrick, if new Airline is added to original table will be it auto added to new query Thanks John

Hey Patrick, I want to use a parameter to filter Top N output of a matrix ; using the parameter slicer. Could you please show me how? Thanks

What happens if with time a new airline name appears in the source file? Will it be added automatically to the "Airline" table? Or will it result in an airline name without a related key?

Hi Patrick ! love the video ! Just one question that I couldn't find answer to : does matching on Id's rather than Airplane name improves the performance of the model ? Thanks à lot

What if I want to make two columns as my primary key, I mean, instead of doing just Airline Name as a Primary Key, I want Primary Key and Claim Site, both of them as my primary key?

What if we have to consider multiple columns for this approach? Also, if we have to use a table as global filters across different sheets developed using different table. I.e. star schema.

Hi Patrick can please do a video on aggregation , i have created an aggregated table using fax and want to create a dynamic filter for a column not included in the aggregation table

Cool! Tks.

From data optimization (and even analysis) perspective it does not make much sense to make a single column dimension, does it? PowerBI already does dictionary compression at its vertipaq storage engine. For 2 or more columns though it might make it easier for the user to use later on, for sure.

Hey Patrick, what if new airlines get added to the flat table? Will it update the index table? Or do we have to do all this process again?