With then go watch that video uh in this video we're focused on a scenario where the values are missing so let's say we are expecting this whole range of data and this is from 2014 2016. but as you.
Can see here there's this whole bunch of data from 2015 which is just missing right so this is all you get so there's a values missing and of course if you look at this in a table you're going to.
Notice that all of those values are showing up under blank now this can happen with a calendar table or it can happen with another kind of table with you know in this case is color and so.
Forth and you can see the problem is whole bunch of values showing up under blank so how do you deal with that let's talk about that my name is avi and if you are just.
Subscribe and click that bell so you don't miss a thing also you can follow me on linkedin and that's where i'm most active so we are in the power bi desktop and.
Before we jump into it i just want to call out that this series of questions while series of videos are based on a question asked by lance about debugging relationships and lance is one of our.
Learned power bi members if you want to find out more about the learnpowerbi.com family well just go to learnbarbi.com right so let's go into a relationship and again sometimes you may.
See and it may seem like the values are missing now what i always advocate as i mentioned in the part one of this video is that always debug things in a table and unstable at least we can see that.
They're not kind of missing they're just ending up in this blank scenario now this turns out is one of the easier scenarios so let me pull that up.
Posts Related:
All right so let's first understand
Actually the tables that are involved so in this case we have the sales table and the calendar table let me see if i have a layout for that i do right so the.Sales table is here and this is our data table and then we have a lookup table which is the calendar table now if we go to our data table this is the sales table and i'm going to sort these values.
And you can see that the values kind of start it's it's a little hard to see there we go so they start in 2014 and we can see that they go all the way.
To 2016. so that's the span of our data values now if you go to our calendar table the lookup table again usually the uphill one is the lookup table that's how we like to arrange so in the.
Calendar table if we look at the data well um yeah let's sort by date there we go well not quite let's try again all right after a few clicks and.
This if you can notice that this starts with january 2015. all right there you go right so 2015. so this is a classic case where the lookup table which is the calendar table in.
This case the the span of the date range or the data range right the key that is being connected is isn't quite the same as the data table so the data table has 2014 15 16 and the calendar table only.
Has this data so of course the fix is simple so if you're missing something in the lookup table which is the calendar table in this case well it has to have all the values in here and it can have.
More so it needs to be a superset now this can potentially go back to 2013 2012 that doesn't hurt and it can potentially go in the future as well now that sometimes does have some.
Repercussions you need to know uh what
You're doing sometimes you can see future dates but you know i often have calendar tables which go off way into the future 2020 2025 right so so this is.The fix that we need to do right now so let's go in and we are using of course our ultimate calendar table if you are not familiar with that well uh you definitely want to check it out.
So that's going to be on my channel the ultimate calendar download video so go go find this video there so let's go back to our query editor and in the calendar table we have.
Let's see the start date here and i'm just going to change that to 2014 hit enter and close and apply and i'll wait for the data to load.
All right and now you can see that we don't have any blanks in here in the table and of course if we go on the graph well um you know it's still colored red because i colored it red but.
Yeah you can see the data matches the expected data range so that was our first scenario i'm just going to talk briefly about the future dates again so generally as i said i do have future.
Dates in the calendar i like that flexibility in case my client or customer just asks me for a projection hey can you show me next year and i don't want to go back and kind of mess.
Or change i have to change my calendar table i can just add a measure which shows the projection but again sometimes it it can have some issues where you don't.
Want to show future dates but that can be dealt separately so let's walk into our next scenario which is slightly different so here we go to a relationship view.
And i'm going to go to layout 2 now in this case the product table is really our data table and we have a colors table so let's check those out so the product table if you go in here.
Kind of looks like this and it has a whole bunch of different datas yeah data stuff yeah but the main thing is the color and you can.
See it's got a whole bunch of values in here and now if we go to the colors table we can see that this has kind of the color hex rgb and the color swatch which.
Is a url so let's see how we can debug this one all right so the first thing i'm going to look at is if we go to our color table.
Then it seems like we do have blue and black in here but in you know so again we the the lookup table has to have all the values in the data table but again.
The the the problem is the same or similar so the lookup table only has a specific range of values in there and the data table has a wider range of values so some are missing from the.
Lookup table but again the key thing is the blue and black are present in our table we can see that right here then why isn't it showing here now it's it's one of those things you you.
Run into the hard way and then you know what to do which is to trim white space and that can really obviously make relationships not work so.
If you go into colors over here we can see uh well if i refresh preview oops let me close the file and and sometimes if you're really.
Careful you can click on the value and then you know you can kind of see that there's a little bit of space there but sometimes it's really hard to tell and of course there can be other like.
Non-printing characters and things like that now generally what i do is if i do run into issues like that then i come in here and go to transform and i do two things the first thing that.
I do is trim so i'm going to zoom in so you can look better right so trim so that's the first thing and the next thing that i do is i do clean as well which as it says removes these non-uh.
Non-printable characters right so trim and clean and of course i mean you know often you don't really see any difference with clean because they're not printable characters but it's there.
It does this magic so trimmed and cleaned and this should take care of the blue and black problem let's wait for that all right there we go so things are.
Looking much better so blue and black are showing up so this is the next thing that i look for now there is another problem here which uh as we saw that there are some colors which are really.
Not colors like multi is really a color na is not really color silver black and and maybe that's the reason that they do not exist in our color table so how do you fix that now of course there can be.
Two ways to fix it one is you go back to the source wherever you're sourcing this colors table from in this case it's an excel file but maybe you're sourcing from your a production system as a data.
Of your house right and and you have somebody make a change there and you tell them that hey these things need to be added in the source and then all you have to do you don't have to do anything.
You just have to click refresh and bingo now your colors table will have that so that's one option where you fix it at the source the other option is you fix it in your.
Model so this point is important so what i'm going to do is i'm going to pull up the query dependencies view and just so i can really emphasize this.