Sumifs and power query Sumifs

[Martin sherk]

I wonder why in the normal Sumifs formula it will ignore the zero at the beg. of an invoice no., while sumifs in power query using aggregate won't ignore it


For Ex.

Sumifs between bank Data and ERP Data will show the below invoice as matching although in the Bank data the invoice doesn't have zero in the beg. (14071) while in ERP data it starts with zero (014071) and sumifs formula matches them with no problems

Invoice No.	Entity	Customer	Currency	Amount	ERP (Sumifs)
14071	USA1	Almada	USD	400​	-400​

Invoice No.	Entity	Customer	Currency	Amount	Bank (Sumifs)
014071	USA1	Almada	USD	-400​	400​

while the same thing with power query will not recognize the below invoice because it starts with zero in the ERP invoice column (014071) while it doesn't start with zero in the Bank invoice column (14071).

Invoice no. Bank	Entity Bank	Customer Bank	Currency Bank	Amount Bank	Sum of ERP.Amount
14071	USA1	Almada	USD	400​	-

Invoice no. ERP	Entity ERP	Customer ERP	Currency ERP	Amount ERP	Sum of ERP.Amount
014071	USA1	Almada	USD	-400​	-

why the Power query doesn't match invoices if there is a zero, like in Bank it's 14071, ERP 014071 while the Normal sumifs formula can recognize and match them with no problems even though one inv. starts with the zero and the other doesn't.

 

[JGordon11]

Try wrapping both Invoice no. references in Power Query with the Number.From function. Example

Number.From([Invoice no. ERP])

 

[Martin sherk]

Try wrapping both Invoice no. references in Power Query with the Number.From function. Example

Number.From([Invoice no. ERP])

i did so, but now when merging as new, i face the below error message

 

[JGordon11]

It may be that your invoice number are not as depicted in your original post. What do the two sets of invoice numbers actually look like?

 

[shift-del]

Hi Martin

Why would you need both solutions?

The dash makes the value a text value which can't be converted.

You could try avoiding the "countif-bug":

The Countif bug (and how to avoid it)

The Countif function counts the number of cells in a range that match some criterion. If we enter 1,2,3 in cells A1:A3 and enter =COUNTIF($A$1:$A$3,A1) somewhere else, it will return 1: But if we …

newtonexcelbach.com

 

[Martin sherk]

It may be that your invoice number are not as depicted in your original post. What do the two sets of invoice numbers actually look like?

it's a huge list of invoices, but i was wondering about invoices that start with zero, after adding your solution it worked but faced another error which was Dataformat.error

 

[JGordon11]

maybe try wrapping both Invoice no. references in Power Query with the Text.TrimStart function with "0" as second parameter. Example

Text.TrimStart([Invoice no. ERP], "0")

 

[Martin sherk]

maybe try wrapping both Invoice no. references in Power Query with the Text.TrimStart function with "0" as second parameter. Example

Text.TrimStart([Invoice no. ERP], "0")

Thank you sir ! it worked.