Look at a relationship first so here we have a relationship between colors and the product table and if we look at the product table that has all of these colors which we are linking to.
Our lookup table color which doesn't have some color so it's missing some colors so again if we look at the master this is the lookup table it doesn't have all the colors that are in the data.
Table and you can see the ones that are missing they are silver black n a and multi now of course in the last video we mentioned that one way to fix that is you just go to the source wherever this.
Table is being sourced from you go there and and and fix that but the challenge is that sometimes you don't have control over that i mean you're sourcing it from.
These tables are not just being used by your team they might be used by other people as well and what works for you may not work for somebody else it might mess something up.
For example in this case they might say that oh you know these are not true colors silver black is not really a color and multi is definitely not a color n a is not a color right so so we.
We we can't it's not the right thing to do so it can really turn you down so so again and and that's why power bi is so amazing because.
You're not at a dead end you can still fix it in your model so that's what we're going to look at today my name is avi and if you're just getting started or keep getting stuck in.
Power bi then this channel is for you because you're gonna find here videos with simple explanations that are that help you go from beginner to pro so make sure to subscribe and click that bell so.
Posts Related:
You don't miss out a thing and while
You're at it make sure to also follow me on linkedin that's where i'm most active all right folks let's get started and as i said earlier this is part three so.We're gonna link to the part one which covers relationships not working at all part two which is relationships kind of work but they're missing values um and now we're on part three now this is all.
Based on a question asked by one of our learned power bi members lance so thank you lance for your question and let's dive into the scenario so again in in our scenario we don't have all the.
Colors here so what we're going to go here is go to our kitchen of power bi which is the query editor all right so here we're going to start.
With a product table and do a reference to that now again what we're trying to do is we're trying to combine the colors which are inner colors table those are good but there.
Are some that are in the product table in the data table and they're missing so we're we're focusing on that so let's uh go to our query editor again so in here product i'm going to change it i'm going.
To call uh colors right so i just change the name product colors and here what i'm going to do is i'm going to keep only the color column.
Remove all the other columns and i'm going to come in here and remove duplicates if i can find it there it is and now what i can do is.
I i'm i can uh let's merge it with the colors table so i'm going to come in here and merge with the colors table i'm going to.
Select the columns
And it's saying that some of them are matching some of them i'm not matching now those are the ones that i'm after now if you really want you can find an.Option in here which will only show the rows that are in product but not in colors and that option is that should be this one left anti but i'll tell you i.
You know i i often don't use that at least in this case you don't have to i just do the regular old merge that way i don't have to worry about the merge.
Type and then i just do processing here again it's just a personal preference i find this to be simpler so again when i'm doing a merge i don't worry about all of this joint kind i just let.
It do it the default which is good for me and then when i come in here i expand the color that's the only one so again what i'm saying is that match it match.
The product colors with this color and then show me what you found right so and and so again i like seeing this because it shows me step by step it sometimes makes it easier to debug and i can see.
Very clearly here that you know these things did match uh now there is one problem with black and blue which we had fixed.
Earlier and i don't know what happened oh i think i overwrote those changes so let me quickly fix that okay so i added the trim and clean which.
Had accidentally removed and now we can see that it is matching those but then these uh multi and silver black and n a they're all showing up as null so again i like doing.
It this way but now i can filter it down to just that so i can say yep i only want to keep the null because those are the ones which exist in product colors but are not in my lookup table right so.
Again now think about this we have the color table which is this and we have the product colors and all we have to do is to append these two together so i can go to either table and what i'm going to.
Do is just say append queries as new and i'll say colors and the product colors and this is the final color table now of.
Course i don't want to change the table name so i'm going to go back here and just change it to original and come back here to append one and.
This is the new colors table and what i'll also do is that really these are throwaway tables by the way i love looking at the query dependencies view so let's take a look at that and how we.
Are building the final colors table so now if i click on colors you can see that it is coming from source but we knew that source wasn't good enough so we are getting.
That data but then you can see that we're also go into the product table and if there are missing values we're get grabbing them from the product colors table as.
Well which is incredible so uh so right and you can see kind of the multi n a and silver black here but we don't need these tables so these ones that i showed you in the query dependency view.
These are just throwaway tables these are just there to for us to be able to generate the colors table which we have right so that's the only that we need so again what i did.
Was i right click and and unchecked this enable load box so because i don't need this in the model okay so all you have to do now is hit close and apply and it's going to be.
Loaded back into our data all right our data load is done and uh but our table kind of went away that can happen sometimes when you you.
Know we really changed the table that we loaded so originally we were loading this one and then we have a new one even though it's called the same name as before.
Power bi kind of detects that uh so all we have to do is just bring it back again and the relationship is already detected and if you go back here and again we can remove the color.
Reference and bring back the color from here and there we go we have no blank values now let's talk about a few things here for one if we look at the color table you would notice that we got the.
Color but the other values are blank now there can be two ways to go about this either you can go to your query editor again and just put like n a or something some some value to.
Indicate that it's not available so you can put some value in there or well the only other option could be is that if somehow the data table which is again is in this.
Case product table that's where we were sourcing the colors from somehow has the additional information like somehow it has the color and the color rgb and other columns.
You know the swatch and all that then of course you can bring those columns in as well right so uh those uh those are the key options and the other part was that this.
This could be problematic so again i kind of brushed it aside and i said oh we changed the tables so the table was gone but you notice that what the impact was.
For one we needed to bring in the new table uh create the relationship but the biggest part was that my report if you remember this this was broken right so when i clicked on this the field was.