Merge Issue | Replace NULL

[captainlaw]

Hello Power BI Community,

I'm trying to merge two table in Power BI queries, and I used full-outer join.  As you can from the result screenshot below, It didn't quite merge the two, so I tried to add a Custom Column trying to replace null values from the other column.  Below is the syntax I used - Text.Replace([State],null,[State.1])

I'm receiving error that null cannot be convert to text. 

I'm wondering how best to merge the two or what syntax I should use to replace all null? 

Appreciate your help.

[v-caliao-msft]

Hi @captainlaw,

In Power BI Desktop, there are multiple options available when using Merge Query feature.

To remove the null value, you can use inner join instead.

Reference
https://businessintelligist.com/2015/09/15/power-bi-desktop-merge-query-options/

Regards,

Charlie Liao

[captainlaw · Accepted solution]

It wasn't an issue with Cap Letters nor JOIN nor Merge.  When I simply Merge again with FULL JOIN, it finally worked.  I am also able to create a DimState from the merged table with combination of IF and Table.Distinct() 

Issue resolved.  Thank you all.

[CahabaData]

I would suggest if the record set model is a reasonable record count and not gigantor: Append (not merge) 1 column to the other for a single long column.  Then create a column using DISTINCT or VALUEs so to eliminate all duplicates.

[kcantor]

You aren't getting a clean merge because your data is not an exact match. Some are in All Caps. Clean the data and transform to make them match, then Merge again. You can also replace the null before creating a new column. Replace null, for example, with 1 the use that in your column instead of null.