Merge Query Issue : Data for few rows not loaded for same key value

[Anonymous]

Hi All,

As you can see in the below screenshot. I am trying to fetch some normalized Sales Role from a dimension table for the Left 2 column combination (Sales Role + Region). I am using left outer join to merge query. The values are populated for a combination of value:

Sales Role = Project Engineer Jr , Region = CoE for some values while they are null for others. Can anyone explain this behaviour?

I tried cleaning and trimming the data as well but it is not working.

[Anonymous]

Hi@Anonymous，

Nice day! Has your problem been solved? if so, please consider Accept a correct reply as the solution to help others find it.

Best Regards,
Caitlyn Yan

[parry2k]

@Anonymous at least we know there is a data problem, and now fix is tricky. Click in the cell where value is correct, PQ shows values in the bottom and copy it and do a similar thing where value is not correct, do you see the length is different because of nonvisible character maybe. For the screenshot, it looks like all the values are in the Proper case, so the case doesn't seem to be the issue. At end of the day, there is some special character that is causing the issue and needs to fix it.

[Anonymous]

@parry2k 

I found it is for if [Sales.Role] = "Project Engineer Jr" 

[parry2k]

@Anonymous let's first check each one individually to see which column has a data quality issue, add two columns and check each individually

if [Region] = "CoE" then 1 else 0

if [Sales.Role] = "Project Engineer Jr." then 1 else 0

[Anonymous]

@parry2k 

 Bingo. I think something is there hiding behind even after the clean/trim. Any trick to clean the same?

[parry2k]

@Anonymous I think I gave you the wrong condition check, if should be AND not OR, with OR if one is true we will get 1, we want to make sure both are TRUE

 if [Region] = "CoE" and [Sales.Role] = "Project Engineer Jr." then 1 else 0

[parry2k]

@Anonymous I assume this merging step is after the clear/trim step? Are you doing this clean/trim on the Dim table as well. Has to be some special character that values are not matching.

Can you add a custom column to check the value by saying if [Region] = "CoE" or [Sales.Role] = "Project Engineer Jr." then 1 else 0 and we expect to get 1 for each row if there are some 0, which means some values are not matching.

just a suggestion to validate the data quality.

Check my latest blog post Improve UX: Show Year in Legend When Using Time Intelligence Measures | PeryTUS IT Solutions  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

@parry2k 

Thanks Parry for your reply. Yes I performed the trim and then executed the merge. I perform the validation you mentioned and you can see all the values are 1 ( Region = 'CoE' and Role = 'Project Engineer Jr'.

When I am visualizing the data using the relationship then it tends to work, whereas the data goes missing when I am merging with the fact table. It's a strange behaviour.

I need the normalized role to be added in the table in order to further merge with a seperate table with multiple key.