paxcarbon.blogg.se

We, therefore, went ahead and created a unique identifier ourselves by using different criteria, in order to create something unique to look for. In this case, the database didn’t contain any sort of unique identifier or information. “But why even bother combining information in the database?”ĪNY look up function needs a unique piece of information to work.

So combining last name and date of birth is the smarter choice as this creates a unique identifier in most cases. There are two David Jones’ in our data, but only one employee with last name Jones and birthdate 07-23-1991. However, if we combine “Last name” and “Date of birth” we would get Jones 07-23-1991. If we combine “First name” and “Last name” we might end up with a David Jones. However combining last name AND date of birth greatly increases the chances of finding a unique value. Unfortunately, none of the information in the employee database is unique, not even last name or date of birth. You might recall that I told you that any lookup function needs to look for a unique piece of information. I am hooked on Power BI and enjoying the journey.After this, I mark where I want my criteria and where the formula goes by writing “Last name” in I2, “Date of birth” in I3 and “Salary” in i4.Īdditionally, I hold the left mouse button down on the line between the column names and drag to make them wider, so that the entire content of the cell fits. If there is a better way to do it - PLEASE reply and let me know. Just remember - this is how it made sense to my brain :-). If it helps you - great!! If there is an error in it somewhere, please let me know. There could also be other coding or DAX formulas that will do the job, but this is just one take on it that worked for me. There were also some other bits and pieces I had to figure out to make it work for me. Many thanks to whoever posted it, but I still had to figure out how everything links together since I am a newbie to M Code. I got the initial code from this forum - cannot remember which post it was & it was before I even was a registered member. I am using the Create a Custom column function to do this.

Just thought I'd make this post to save someone (new to Power BI like me) some time to figure out how to make the MS Excel Index/Match formula work in Power Query editor without using the Merge function. This is my very first contribution type post on the forum - so please be kind and bear with me if I made a mistake somewhere.