How to merge tables and include unmatched records as 0 instead of null?

Question

I have two tables in Power Query: Analytics and Inventory. I need to merge them together, so that the Analytics records that exist are added to the Inventory table, and where there is no match, the "Visits" column becomes a 0. I am using Page/URL as the linking column.

Inventory Table

URL   | Created By
Page1 | John
Page2 | Mike
Page3 | Scot

Analytics Table

Page  | Visits
Page1 | 12
Page2 | 10
Page5 | 5

Desired Results Table

URL   | Created By | Visits
Page1 | John       | 12
Page2 | Mike       | 10
Page3 | Scot       | 0

Before I do the merge in Power Query, I cleaned the data in Page/URL by using Text.Trim, Text.Lower, Remove Rows (Remove Duplicates and Remove Blank Rows), and Table.SelectRows to remove any records with a null Page/URL.

I did a Left Outer Join with Inventory :: Analytics but the Visits show up as "blank" instead of 0 when I look up the visits by page. I tried to solve this by adding the following code after the merge in Power Query Table.ReplaceValue(referenceToAboveLine, null, 0,Text.Replace, {Analytics}) but that resulted in the query taking extremely long and I had to time it out.

Where I am going wrong? Am I using the wrong Join method? Is there a better way to replace the null values after the merge?

Is there a reason you need a 0 instead of null? If so, is there also a reason you're storing integers as text ("0"). — Alexis Olson
@AlexisOlson Edited the quotes out, as it is intended to be integer and not as text. And need the records to be 0 so I can safely run calculations on the field, and also because that any unmatched record is a 0 in the visits count, and end users will understand 0s but not necessarily what null means — Moses

Alexis Olson Alexis Olson · Accepted Answer · 2019-04-04T21:47:00

DAX handles nulls pretty well, in my experience, so I'm not sure you need to convert for calculation purposes, but let's suppose you do.

Instead of Table.ReplaceValue, maybe try Table.Transform?

Table.TransformColumns(
    referenceToAboveLine,
    {{"Visits", each if _ = null then 0 else _, Int64.Type}}
)

How to merge tables and include unmatched records as 0 instead of null?

1 Answers