Counting differences between two sql tables

[ramirefm]

Hello, I am trying to return the number of differences that there are between two tables that I have in a sql database. They are both nearly identical besides the actual values. So the column and row names are exactly the same as well as the amount of columns and rows. I've tried to directly compare columns however it gives me an error: 

CountIf(Patients, Patients.FirstName <> PatientEdits.FirstName);

Ideally I want to be able to count all differences between one row from both tables.

[PowerRanger]

@ramirefm @So you want to compare row 1 of table 1 and row 1 of table 2…. Row 2 of table 1 and row 2 of table 2 ….and so on. Or do the tables have a column which must be used to find the rows belonging to each other?

[ramirefm]

Yes, you've got the right idea, I want to compare row 1 of table 1 and row 1 of table 2 and so on. I don't need anything with the columns as the rows should already be aligned with each other.

[PowerRanger · Accepted solution]

@ramirefm 

I think I got it working but I am totally unsure if this is a "good" approach.

I tried it like this. First I created 2 initial collections. Based on those initial Collections I created two new working collections with one addition column where I just concatenated all columns

ClearCollect(colListA,{FirstName:"Lutz",LastName:"Beyer"},{FirstName:"Max",LastName:"Mustermann"},{FirstName:"Power",LastName:"Ranger"});
ClearCollect(colListB,{FirstName:"Lutz",LastName:"Beyer"},{FirstName:"Max",LastName:"MusterFrau"},{FirstName:"Power",LastName:"Ranger"});

ClearCollect(colListACombinded,AddColumns(colListA,"Combined",FirstName&LastName));
ClearCollect(colListBCombinded,AddColumns(colListB,"Combined",FirstName&LastName));

I then compared the working collections like this

Clear(Counter);
Clear(rowErrors);
ForAll(
    colListACombinded,
    Collect(
        Counter,
        Last(Counter).Value + 1
    );
    If(
        Last(
            FirstN(
                colListBCombinded,
                Last(Counter).Value
            )
        ).Combined = ThisRecord.Combined,
        Collect(
            rowErrors,
            {
                row: Last(Counter).Value,
                HasError: false
            }
        ),
        Collect(
            rowErrors,
            {
                row: Last(Counter).Value,
                HasError: true
            }
        )
    )
);

As in PowerApps we can't get an item based on a index we have to use this "ugly" Last(FirstN..." part.

Within the ForAll I add records to the collection rowErrors. If the Combined columns don't match HasError will be true otherwise false.

You can add three Galleries on your screen and one Button. Paste the Complete code in the OnSelect of the button. Point your Galleries to colListA, colLisB and rowErrors

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[PowerRanger]

@ramirefm 

To get the amount of Errors place another label on the screen and set its Test property to

"Errors:" & CountIf(rowErrors,HasError = true)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[PowerRanger]

@ramirefm 

To get the Errors on column level you could try this:

Clear(Counter);
Clear(columnError);
ForAll(
    colListACombinded,
    Collect(
        Counter,
        Last(Counter).Value + 1
    );
    If(
        Last(
            FirstN(
                colListBCombinded,
                Last(Counter).Value
            )
        ).FirstName = ThisRecord.FirstName,
        Collect(
            columnError,
            {
                row: Last(Counter).Value,
                HasError: false,
                Field:"FirstName"
            }
        ),
        Collect(
            columnError,
            {
                row: Last(Counter).Value,
                HasError: true,
                Field:"FirstName"
            }
        )
    );
    If(
        Last(
            FirstN(
                colListBCombinded,
                Last(Counter).Value
            )
        ).LastName = ThisRecord.LastName,
        Collect(
            columnError,
            {
                row: Last(Counter).Value,
                HasError: false,
                Field:"LastName"
            }
        ),
        Collect(
            columnError,
            {
                row: Last(Counter).Value,
                HasError: true,
                Field:"LastName"
            }
        )
    )
);

Add another Gallery on your screen and sets its items property to columnError

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[PowerRanger]

@ramirefm 

This would be my final approach for column comparison:

Clear(Counter);
Clear(columnError);
ForAll(
    colListACombinded,
    Collect(
        Counter,
        Last(Counter).Value + 1
    );
    With(
        Last(
            FirstN(
                colListBCombinded,
                Last(Counter).Value
            )
        ) As relatedRecord,
        If(
            relatedRecord.FirstName = ThisRecord.FirstName,
            Collect(
                columnError,
                {
                    row: Last(Counter).Value,
                    HasError: false,
                    Field: "FirstName"
                }
            ),
            Collect(
                columnError,
                {
                    row: Last(Counter).Value,
                    HasError: true,
                    Field: "FirstName"
                }
            )
        );
        If(
            relatedRecord.LastName = ThisRecord.LastName,
            Collect(
                columnError,
                {
                    row: Last(Counter).Value,
                    HasError: false,
                    Field: "LastName"
                }
            ),
            Collect(
                columnError,
                {
                    row: Last(Counter).Value,
                    HasError: true,
                    Field: "LastName"
                }
            )
        )
    )
);

So for each column you want to compare you have to add another:

If(
            relatedRecord.<column name> = ThisRecord.<column name>,
            Collect(
                columnError,
                {
                    row: Last(Counter).Value,
                    HasError: false,
                    Field: "<column name>"
                }
            ),
            Collect(
                columnError,
                {
                    row: Last(Counter).Value,
                    HasError: true,
                    Field: "<column name>"
                }
            )
        )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[RandyHayes]

@PowerRanger 

So I believe earlier we were having a discussion about the ForAll and the power of it.  When used like the ForLoop that you have in the formula, it is quite a "so-so" formula.

When used in the way it was intended, it becomes quite powerful and your formula on your Gallery can become simply this:

With({_listA: Table({FirstName:"Lutz",LastName:"Beyer"},{FirstName:"Max",LastName:"Mustermann"},{FirstName:"Power",LastName:"Ranger"}),
      _listB: Table({FirstName:"Lutz",LastName:"Beyer"},{FirstName:"Max",LastName:"MusterFrau"},{FirstName:"Power",LastName:"Ranger"})},

      
      Filter(
        ForAll(Sequence(CountRows(_listA)),
            With({_listItemA: Last(FirstN(_listA, Value)), _listItemB: Last(FirstN(_listB, Value))},
              {Comp: (_listItemA.FirstName = _listItemB.FirstName) &&
                     (_listItemA.LastName = _listItemB.LastName),
               itemA: _listItemA,
               itemB: _listItemB
              }
            )
        ),
        !Comp
      )
)

A label in the Gallery to show the first and last name of list a, and a label to show the different record first and last name in List B.

No collections needed and no behavioral actions needed. It will just show the records that are in error.

To get counts, if used from a gallery, then CountRows(galleryName.AllItems) will give you the count. 

Just thought I would throw this out there as we had discussed earlier, and this is a good example about how to use the ForAll correctly.  Makes a big difference!

Hopefully this will be helpful for you.

[PowerRanger]

@RandyHayes I knew you will comment this because of our discussion before 🙂 and that’s the only reason why I said “totally unsure if this is a "good" approach.”

Still trying to get my head around on how to use ForAll correct…. At least I believe I understood the power of With now 🙂

[RandyHayes]

@PowerRanger 

Lol...too funny!  I wasn't trying to intrude, just saw the formula and, said - there's a good example of a ForAll backward...🤣

I used a With in that formula just to encapsulate the data, but it could easily be a datasource, a Gallery, or any other table producing item.

I will say as a side, there is one ability that is lacking in PowerApps...the ability to compare a record.  It would be ultra-nice to be able to do a recordA = recordB to determine if they are the same.  Now, I fully understand why it does not exist...but it would be nice!