Power Query - Custom Column To Count

[cbjroms]

Relatively new to Power Query and have spent a few hours trying to solve a very simple issue.

1 have 2 (sql queries) which I am loading and merging in Power Query.

One query lists Purchase Order Numbers and their gross total value. Obviously it has one row for each PO Number.

The other query lists payments made against each PO Number and this table can have multiple rows for each PO Number.

I can merge the queries and group rows by PO Number which gives me a table showing which POs have been either paid or part-paid without any problems.

But I want to add a column which shows how many payments have been made against each PO Number. I have tried adding a new aggregation to the Grouped Rows (Name: Number Of Payments Operation: Count Rows Column: Blank) but this produces a very strange result. For a PO Number with no payments made it returns 1. For a PO Number with 1 payment made it returns 1. Yet for a PO Number with 2 payments made it returns 2.

Why is it rettuning 1 when the PO Number does not appear in the payments query?

[Anonymous]

Sounds like you are merging first then grouping and keeping all rows from your PO table, therefore you will always have one row for each PO. Might want to group your Payments table by PO number first and then merge.

[rubayatyasmin]

Hi, @cbjroms 

The behavior you're experiencing might be due to the way Power Query handles null or empty values during the merging process and aggregation. When you merge your queries, Power Query may create null values for the rows where there is no corresponding payment in the second table. When you count rows after grouping, Power Query counts null values as 1.

so, filter out null before grouping and counting. If filtering is not an option then create a conditional column like 

= if [Payment Column] <> null then 1 else 0

then croup and count.