Merge Queries gives double data on left table and gives null values from right table

[TcT85]

Hi,

I have tried so many things without being to solve this issue.

Left table have the product name and component name and position.

Right table has component name and number of soldering points for specific component name.

The issue is that that the expanded column gives me double data of componet C0533 and TB0837 and gives null values under column Compontnens.SOldering_points.

This is my SQL query :

SELECT bh.[PartNo],
REPLACE(LTRIM(RTRIM(b.[ItemID])) + '-' + LTRIM(RTRIM(b.[ConfigId])),'--','') AS [ComponentID],
p.[Position]

FROM [ProductBOM].[dbo].[VW_BOM_HEADER] bh WITH (NOLOCK)
LEFT JOIN [ProductBOM].[dbo].[VW_BOM] b WITH (NOLOCK) on b.[HEADER_RECID] = bh.[RECID]
LEFT JOIN [ProductBOM].[dbo].[TBL_BOMPosition] p WITH (NOLOCK) on p.[BOMRefRecId] = b.[RECID]
WHERE p.[Position] IS NOT NULL
AND p.[Position] != 'PCB'
AND bh.PartNo = '1611-101-06'
ORDER BY bh.[PartNo] ASC,
[Position] ASC,
b.[ItemID] ASC

1. Here is the Data source untouched.

2. Second step Left Outer

3. Expand 

[vs_7 · Accepted solution]

hi @TcT85 ,

try below steps:

1. Check for duplicate rows in the left table: It's possible that the left table contains duplicate rows, which can result in duplicate values after merging. You can remove duplicate rows by using the "Remove Duplicates" option in the Power Query Editor. To do this, select the left table, go to the "Home" tab, and click on "Remove Rows" > "Remove Duplicates."

2. Verify the join conditions: Double-check the join conditions used in the merge operation. Ensure that the join columns are correctly specified and that they match the corresponding columns in the right table.

3. Check the join type: Make sure that you are using the appropriate join type for your scenario. In your case, since you're using a left join (LEFT OUTER JOIN in SQL), all rows from the left table should be included, even if there are no matching records in the right table. If you need to include only the matching records, you can use an inner join instead.

4. Verify the data types and values: Ensure that the component name columns in both tables have compatible data types and values. Differences in data types or values can lead to null values after the merge. Check for leading or trailing spaces, case sensitivity, or any other inconsistencies that might affect the matching process.

5. Perform data profiling: Use the data profiling capabilities in the Power Query Editor to analyze the data in both tables. This can help identify any anomalies or unexpected values that might be causing the issue.

[TcT85]

hi @vs_7 ,

You were right, the original data had some duplicate component name with 2 different values null and numbers.

This is now solved, thank you!