CODEWITHSUNDEEP
powerbipowerquery
Moses
Moses

Reputation: 9173

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

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?

Upvotes: 0

Views: 3034

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

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}}
)

Upvotes: 2

Related Questions