How can I solve this data quality issue?

[ERing]

I have a report like below where I'm showing some marketing data. My Fact_Table is connected to my Marketing_Channel_Mapping Table on Marketing_Channel_ID.

My end previously has this report in Excel and they were including the "Blank" Marketing Channel under the "Low" Channel Category.

I first tried to solve this using a conditional column on my Marketing_Channel_Mapping by updating the "Channel_Category" to be "Low" for any records where Marketing_Channel was blank. This is when I realized that there are NO records with Blank Channel_Category and blank Marketing_Channel.

I've found that the issue is that my Fact_Table has records where there is no Marketing_Channel_ID and therefore doesn't have a match to a Marketing_Channel_ID in the Marketing_Channel_Mapping table.

This is resulting in the row with blank Channel_Category and Marketing_Channel, but this is only happing after the join is made in the model view of PBI. This leaves me no way to correct this in Power Query (that I know of).

Any suggestions on how I can correct this? The desired end result would be for the row with "Blank" Marketing_Channel to be shown under the "Low" Channel_Category.

Sample File 
Excel Sample Data 

[v-shamiliv]

Hi @ERing 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-shamiliv]

Hi @ERing 
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-shamiliv]

Hi @ERing 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-shamiliv]

Hi @ERing 
Thank you for reaching out microsoft fabric community forum
I have attached Pbix for reference .please go through it.

If this solution helps, please consider giving us Kudos and accepting it as the solution so that it may assist other members in the community
Thank you.

[Thejeswar]

Hi @ERing ,

This Data Quality Issue is because your Fact Table has some Marketing_Channel_Id values that are not present in the Marketing_Channel_Mapping Table.

The Solution is to add the missing Marketing_Channel_Id value to the Marketing_Channel_Mapping table

I have shown a sample representation below.

See Below screenshots

Fact Table:

Mapping Table:

Output:

The Above Blank can be solved by adding the missing Marketing_Channel_Id to the Mapping table

Post Adding Screenshot Below:

Regards,

[ERing]

@Thejeswar Thanks for the reply, however I don't believe what you've described is the issue I have.

I've intentionally set up my data in the PBI file to represent the issue I'm having in my real work file.
The issue is that the Fact_Table has records with no Marketing_Channel_ID. 

I've added the Excel file to my original post if it helps.

[Thejeswar · Accepted solution]

Hi @ERing ,

You can fix this by adding a ID for the null values . This ID should also be part of the Dimension Mapping table.

The Below are the steps

1. Replaces the null values in Fact tables with an ID. In this case, I am replacing with 999

2. Add the new Id to the Dimension table.

Rest all no changes. The Output should look like shown below

Attaching the PBIX here for your reference.

Regards,