Forum Discussion
Dim table’s id with nulls
My view is filter them out. Don't load them. Also generate an audit query that lists any keys in your fact table that are missing in the dim table. That way you know what dims are missing, and you can fix these at the source.
Yes, all of that is true; your scenario suggested that you might want to see these values. How are you going to show them as separate rows if you have no matching ID in your dimension? What if you have 100 nulls in your fact table, worth $20,000? This at least gives you a way to quantify the value of the IDs missing from your incomplete tables. My and everyone else's advice was to fix your dimension table, or at minimum remove the nulls. Simply removing the nulls will give you incomplete totals, as of course you must know.
Had you read my earlier advice more thoroughly, you'd have removed the duplicate IDs from your dimension table so that you had unique values for your star schema.
My advice may have been beyond your grasp, but we offer advice for your "what-if" scenarios because we've dealt with these very issues long ago, many times, effectively. Our solutions make sense, and are even more precise when the question includes specifics, data, and code. If you don't want to implement or don't understand the advice for which you asked, you can simply move on, or seek clarification.
--Nate
If i have 10 unique rows with nulls in my id column for the dim table and I replace the nulls with say 123456 then if i remove the duplicates it will still show all 10 rows meaning the problems still persist unless i remove duplicates on just the id column but that will mean 9 of my 10 unique rows will disappear. I then won't be able to show those 9 values in my report. If it was possible to change a individual row one at a time to 10 unique values then yes that would suit this issue better. But thanks for your advice as well i understand on power bi community with limited information it is hard to know what the person with the question is asking.