Skip to Content

Comparing "Records" in one "Collection" to "Records" in another "Collection" in Power Apps

December 30, 2025 by
Comparing "Records" in one "Collection" to "Records" in another "Collection" in Power Apps
TxTechnician, Andrew Valenzuela
| No comments yet

To compare all Records​ in one Collection​ to another, you have to Concatenate() the data into one column.

Then you just compare the whole "concatenated" data set using the in​ operator.  


Copy and paste this YAML into a Power App.  Or scroll below to copy the individual functions.  

Screen: Compare using concatenate

# Compare collections screen
Screens:
  screen_compare_collections:
    Properties:
      LoadingSpinnerColor: =RGBA(56, 96, 178, 1)
    Children:
      - button_compare_collections:
          Control: Classic/Button@2.2.0
          Properties:
            BorderColor: =ColorFade(Self.Fill, -15%)
            Color: =RGBA(255, 255, 255, 1)
            DisabledBorderColor: =RGBA(166, 166, 166, 1)
            Fill: =RGBA(56, 96, 178, 1)
            Font: =Font.'Open Sans'
            Height: =81
            HoverBorderColor: =ColorFade(Self.BorderColor, 20%)
            HoverColor: =RGBA(255, 255, 255, 1)
            HoverFill: =ColorFade(RGBA(56, 96, 178, 1), -20%)
            OnSelect: |-
              =ClearCollect(collection_1,
              {ID:2,One: "a", Two: "b"},
              {ID:3,One: "a", Two: "b"},
              {ID:4,One: "a", Two: "b"},
              {ID:5,One: "a", Two: "b"},
              {ID:6,One: "a", Two: "b"});
              ClearCollect(collection_2,
              {ID:2,One: "a", Two: "b"},
              {ID:35,One: "a", Two: "b"},
              {ID:3,One: "x", Two: "b"},
              {ID:4,One: "x", Two: "b"},
              {ID:5,One: "a", Two: ""},
              {ID:6,One: "a", Two: "b"})
            PressedBorderColor: =Self.Fill
            PressedColor: =Self.Fill
            PressedFill: =Self.Color
            Text: ="Create 2 Collections"
            Width: =508
            X: =57
            Y: =42
      - gallery_collection_1:
          Control: Gallery@2.15.0
          Variant: Vertical
          Properties:
            BorderColor: =RGBA(0, 18, 107, 1)
            Fill: =RGBA(238, 204, 204, 1)
            Height: =333
            Items: =collection_1
            TemplateSize: =60
            Width: =310
            Y: =218
          Children:
            - Container1:
                Control: GroupContainer@1.3.0
                Variant: AutoLayout
                Properties:
                  Height: =Parent.TemplateHeight
                  LayoutAlignItems: =LayoutAlignItems.Center
                  LayoutDirection: =LayoutDirection.Horizontal
                  LayoutJustifyContent: =LayoutJustifyContent.Center
                  PaddingBottom: =8
                  PaddingLeft: =8
                  PaddingRight: =8
                  PaddingTop: =8
                  Width: =Parent.TemplateWidth
                Children:
                  - Label1:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="ID: 
                          "&ThisItem.ID
                  - Label1_1:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="One: 
                          "&ThisItem.One
                  - Label1_2:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="Two: 
                          "&ThisItem.Two
      - gallery_collection_2:
          Control: Gallery@2.15.0
          Variant: Vertical
          Properties:
            BorderColor: =RGBA(0, 18, 107, 1)
            Fill: =RGBA(204, 231, 246, 1)
            Height: =333
            Items: =collection_2
            TemplateSize: =60
            Width: =310
            X: =330
            Y: =218
          Children:
            - Container1_1:
                Control: GroupContainer@1.3.0
                Variant: AutoLayout
                Properties:
                  Height: =Parent.TemplateHeight
                  LayoutAlignItems: =LayoutAlignItems.Center
                  LayoutDirection: =LayoutDirection.Horizontal
                  LayoutJustifyContent: =LayoutJustifyContent.Center
                  PaddingBottom: =8
                  PaddingLeft: =8
                  PaddingRight: =8
                  PaddingTop: =8
                  Width: =Parent.TemplateWidth
                Children:
                  - Label1_3:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="ID: 
                          "&ThisItem.ID
                  - Label1_4:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="One: 
                          "&ThisItem.One
                  - Label1_5:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="Two: 
                          "&ThisItem.Two
      - Label2:
          Control: Label@2.5.1
          Properties:
            BorderColor: =RGBA(0, 18, 107, 1)
            Fill: =RGBA(238, 204, 204, 1)
            Font: =Font.'Open Sans'
            Text: |-
              ="Col. 1: "
            Underline: =true
            Width: =310
            Y: =148
      - Label2_1:
          Control: Label@2.5.1
          Properties:
            BorderColor: =RGBA(0, 18, 107, 1)
            Fill: =RGBA(204, 231, 246, 1)
            Font: =Font.'Open Sans'
            Text: |-
              ="Col. 2: "
            Underline: =true
            Width: =310
            X: =330
            Y: =148
      - gallery_compared_collections:
          Control: Gallery@2.15.0
          Variant: Vertical
          Properties:
            BorderColor: =RGBA(0, 18, 107, 1)
            Fill: =RGBA(232, 244, 217, 1)
            Height: =333
            Items: =collection_compared
            TemplateSize: =60
            Width: =310
            X: =156
            Y: =773
          Children:
            - Container1_2:
                Control: GroupContainer@1.3.0
                Variant: AutoLayout
                Properties:
                  Height: =Parent.TemplateHeight
                  LayoutAlignItems: =LayoutAlignItems.Center
                  LayoutDirection: =LayoutDirection.Horizontal
                  LayoutJustifyContent: =LayoutJustifyContent.Center
                  PaddingBottom: =8
                  PaddingLeft: =8
                  PaddingRight: =8
                  PaddingTop: =8
                  Width: =Parent.TemplateWidth
                Children:
                  - Label1_6:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="ID: 
                          "&ThisItem.ID
                  - Label1_7:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="One: 
                          "&ThisItem.One
                  - Label1_8:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="Two: 
                          "&ThisItem.Two
      - button_create_collections:
          Control: Classic/Button@2.2.0
          Properties:
            BorderColor: =ColorFade(Self.Fill, -15%)
            Color: =RGBA(255, 255, 255, 1)
            DisabledBorderColor: =RGBA(166, 166, 166, 1)
            Fill: =RGBA(56, 96, 178, 1)
            Font: =Font.'Open Sans'
            Height: =81
            HoverBorderColor: =ColorFade(Self.BorderColor, 20%)
            HoverColor: =RGBA(255, 255, 255, 1)
            HoverFill: =ColorFade(RGBA(56, 96, 178, 1), -20%)
            OnSelect: |-
              =//Add a columns that will be used to compare the values of all keys in the row of data of your collection.  "Concatenate all the columns"
              ClearCollect(collection_1_with_concat_col,AddColumns(collection_1,concatenate_1,Concatenate(ID,"|",One,"|",Two,"|")));
              ClearCollect(collection_2_with_concat_col,AddColumns(collection_2,concatenate_2,Concatenate(ID,"|",One,"|",Two,"|")));


              //This finds items that match
              ClearCollect(collection_compared,Filter(collection_2_with_concat_col, concatenate_2 in collection_1_with_concat_col.concatenate_1));

              //Adding the Not() fx will give the opposite result
              //ClearCollect(collection_compared,Filter(collection_2_with_concat_col, Not(concatenate_2 in collection_1_with_concat_col.concatenate_1)))
            PressedBorderColor: =Self.Fill
            PressedColor: =Self.Fill
            PressedFill: =Self.Color
            Text: ="Compare Col 1 & 2"
            Width: =508
            X: =57
            Y: =608

Screen: Compare using json

# Compare collections screen
Screens:
  screen_compare_collections_with_json:
    Properties:
      LoadingSpinnerColor: =RGBA(56, 96, 178, 1)
    Children:
      - button_compare_collections_1:
          Control: Classic/Button@2.2.0
          Properties:
            BorderColor: =ColorFade(Self.Fill, -15%)
            Color: =RGBA(255, 255, 255, 1)
            DisabledBorderColor: =RGBA(166, 166, 166, 1)
            Fill: =RGBA(56, 96, 178, 1)
            Font: =Font.'Open Sans'
            Height: =81
            HoverBorderColor: =ColorFade(Self.BorderColor, 20%)
            HoverColor: =RGBA(255, 255, 255, 1)
            HoverFill: =ColorFade(RGBA(56, 96, 178, 1), -20%)
            OnSelect: |-
              =ClearCollect(collection_1,
              {ID:2,Two: "b",One: "a"},
              {ID:3,One: "a", Two: "b"},
              {ID:4,One: "a", Two: "b"},
              {ID:5,One: "a", Two: "b"},
              {ID:6,One: "a", Two: "b"});
              ClearCollect(collection_2,
              {ID:2,One: "a", Two: "b"},
              {ID:35,One: "a", Two: "b"},
              {ID:3,One: "x", Two: "b"},
              {ID:4,One: "x", Two: "b"},
              {ID:5,One: "a", Two: ""},
              {ID:6,One: "a", Two: "b"})
            PressedBorderColor: =Self.Fill
            PressedColor: =Self.Fill
            PressedFill: =Self.Color
            Text: ="Create 2 Collections"
            Width: =508
            X: =57
            Y: =42
      - gallery_collection_1_json:
          Control: Gallery@2.15.0
          Variant: Vertical
          Properties:
            BorderColor: =RGBA(0, 18, 107, 1)
            Fill: =RGBA(238, 204, 204, 1)
            Height: =333
            Items: =collection_1
            TemplateSize: =60
            Width: =310
            Y: =218
          Children:
            - Container1_3:
                Control: GroupContainer@1.3.0
                Variant: AutoLayout
                Properties:
                  Height: =Parent.TemplateHeight
                  LayoutAlignItems: =LayoutAlignItems.Center
                  LayoutDirection: =LayoutDirection.Horizontal
                  LayoutJustifyContent: =LayoutJustifyContent.Center
                  PaddingBottom: =8
                  PaddingLeft: =8
                  PaddingRight: =8
                  PaddingTop: =8
                  Width: =Parent.TemplateWidth
                Children:
                  - Label1_9:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="ID: 
                          "&ThisItem.ID
                  - Label1_10:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="One: 
                          "&ThisItem.One
                  - Label1_11:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="Two: 
                          "&ThisItem.Two
      - gallery_collection_2_json:
          Control: Gallery@2.15.0
          Variant: Vertical
          Properties:
            BorderColor: =RGBA(0, 18, 107, 1)
            Fill: =RGBA(204, 231, 246, 1)
            Height: =333
            Items: =collection_2
            TemplateSize: =60
            Width: =310
            X: =330
            Y: =218
          Children:
            - Container1_4:
                Control: GroupContainer@1.3.0
                Variant: AutoLayout
                Properties:
                  Height: =Parent.TemplateHeight
                  LayoutAlignItems: =LayoutAlignItems.Center
                  LayoutDirection: =LayoutDirection.Horizontal
                  LayoutJustifyContent: =LayoutJustifyContent.Center
                  PaddingBottom: =8
                  PaddingLeft: =8
                  PaddingRight: =8
                  PaddingTop: =8
                  Width: =Parent.TemplateWidth
                Children:
                  - Label1_12:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="ID: 
                          "&ThisItem.ID
                  - Label1_13:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="One: 
                          "&ThisItem.One
                  - Label1_14:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="Two: 
                          "&ThisItem.Two
      - Label2_2:
          Control: Label@2.5.1
          Properties:
            BorderColor: =RGBA(0, 18, 107, 1)
            Fill: =RGBA(238, 204, 204, 1)
            Font: =Font.'Open Sans'
            Text: |-
              ="Col. 1: "
            Underline: =true
            Width: =310
            Y: =148
      - Label2_3:
          Control: Label@2.5.1
          Properties:
            BorderColor: =RGBA(0, 18, 107, 1)
            Fill: =RGBA(204, 231, 246, 1)
            Font: =Font.'Open Sans'
            Text: |-
              ="Col. 2: "
            Underline: =true
            Width: =310
            X: =330
            Y: =148
      - gallery_compared_collections_json:
          Control: Gallery@2.15.0
          Variant: Vertical
          Properties:
            BorderColor: =RGBA(0, 18, 107, 1)
            Fill: =RGBA(232, 244, 217, 1)
            Height: =333
            Items: =collection_compared
            TemplateSize: =60
            Width: =310
            X: =156
            Y: =773
          Children:
            - Container1_5:
                Control: GroupContainer@1.3.0
                Variant: AutoLayout
                Properties:
                  Height: =Parent.TemplateHeight
                  LayoutAlignItems: =LayoutAlignItems.Center
                  LayoutDirection: =LayoutDirection.Horizontal
                  LayoutJustifyContent: =LayoutJustifyContent.Center
                  PaddingBottom: =8
                  PaddingLeft: =8
                  PaddingRight: =8
                  PaddingTop: =8
                  Width: =Parent.TemplateWidth
                Children:
                  - Label1_15:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="ID: 
                          "&ThisItem.ID
                  - Label1_16:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="One: 
                          "&ThisItem.One
                  - Label1_17:
                      Control: Label@2.5.1
                      Properties:
                        BorderColor: =RGBA(0, 18, 107, 1)
                        FillPortions: =1
                        Font: =Font.'Open Sans'
                        LayoutMaxHeight: =0
                        LayoutMaxWidth: =0
                        LayoutMinHeight: =16
                        LayoutMinWidth: =16
                        Size: =16
                        Text: |-
                          ="Two: 
                          "&ThisItem.Two
      - button_create_collections_1:
          Control: Classic/Button@2.2.0
          Properties:
            BorderColor: =ColorFade(Self.Fill, -15%)
            Color: =RGBA(255, 255, 255, 1)
            DisabledBorderColor: =RGBA(166, 166, 166, 1)
            Fill: =RGBA(56, 96, 178, 1)
            Font: =Font.'Open Sans'
            Height: =81
            HoverBorderColor: =ColorFade(Self.BorderColor, 20%)
            HoverColor: =RGBA(255, 255, 255, 1)
            HoverFill: =ColorFade(RGBA(56, 96, 178, 1), -20%)
            OnSelect: |-
              =//Add a columns that will be used to compare the values of all keys in the row of data of your collection.  "Concatenate all the columns"
              ClearCollect(collection_1_with_concat_col_using_json,AddColumns(collection_1,concatenate_1_json,JSON(ThisRecord)));
              ClearCollect(collection_2_with_concat_col_using_json,AddColumns(collection_2,concatenate_2_json,JSON(ThisRecord)));


              //This finds items that match
              ClearCollect(collection_compared,Filter(collection_2_with_concat_col_using_json, concatenate_2_json in collection_1_with_concat_col_using_json.concatenate_1_json));

              //Adding the Not() fx will give the opposite result
              //ClearCollect(collection_compared,Filter(collection_2_with_concat_col_using_json, Not(concatenate_2_json in collection_1_with_concat_col_using_json.concatenate_1_json)))
            PressedBorderColor: =Self.Fill
            PressedColor: =Self.Fill
            PressedFill: =Self.Color
            Text: ="Compare Col 1 & 2"
            Width: =508
            X: =57
            Y: =608


The in​ operator:

Docs: PowerFx Docs

The in​ operator is used to match data contained in a collection, table or string.

You cannot compare multiple "records" in a collection to another collection.

With the in​ operator, concerning collections, you can do the following:

single record

​Filter(collection_2,First(collection_1) in collection_2)​

A single record is compared to a collection of records.

multiple values in a column

​Filter(collection_2, ID in collection_1.ID)​

Multiple values in the column ID​ are compared to another collection's column.

Because of this limitation we have to use a workaround to compare multiple records.

The AddColumns()​ function:

Docs: AddColumns() PowerFx

Used to add a column on the fly.  

The syntax is:

​AddColumns(collection_name, new_column_name, <logic or static data>, next_new_column_name, <logic or static data>...)​

The Concatenate()​ function:

Docs: Concatenate() PowerFx

Used to combine strings.  You can combine multiple records values using the &​ (the concatenate operator).  

The syntax is:

​Concatenate("string 1", "string 2", "string 3")​ 
(returns: string 1string 2string3​)

​Concatenate("string 1" & " " & "string 2" & " " & "string 3")​

(returns: string 1 string 2 string 3​

Let's combine these functions to compare multiple records:

Concatenate & Compare

//Create two collections
ClearCollect(
    collection_1,
    {
        ID: 2,
        One: "a",
        Two: "b"
    },
    {
        ID: 3,
        One: "a",
        Two: "b"
    },
    {
        ID: 4,
        One: "a",
        Two: "b"
    },
    {
        ID: 5,
        One: "a",
        Two: "b"
    },
    {
        ID: 6,
        One: "a",
        Two: "b"
    }
);
ClearCollect(
    collection_2,
    {
        ID: 2,
        One: "a",
        Two: "b"
    },
    {
        ID: 35,
        One: "a",
        Two: "b"
    },
    {
        ID: 3,
        One: "x",
        Two: "b"
    },
    {
        ID: 4,
        One: "x",
        Two: "b"
    },
    {
        ID: 5,
        One: "a",
        Two: ""
    },
    {
        ID: 6,
        One: "a",
        Two: "b"
    }
);
//Add a columns that will be used to compare the values of all keys in the row of data of your collection.  "Concatenate all the columns"
ClearCollect(
    collection_1_with_concat_col,
    AddColumns(
        collection_1,
        concatenate_1,
        Concatenate(
            ID,
            "|",
            One,
            "|",
            Two,
            "|"
        )
    )
);
ClearCollect(
    collection_2_with_concat_col,
    AddColumns(
        collection_2,
        concatenate_2,
        Concatenate(
            ID,
            "|",
            One,
            "|",
            Two,
            "|"
        )
    )
);
//This finds items that match
ClearCollect(
    collection_compared,
    Filter(
        collection_2_with_concat_col,
        concatenate_2 in collection_1_with_concat_col.concatenate_1
    )
);
//Adding the Not() fx will give the opposite result
//ClearCollect(collection_compared,Filter(collection_2_with_concat_col, Not(concatenate_2 in collection_1_with_concat_col.concatenate_1)))

Concatenate is best used if you need to compare specific collections in a column.  There is a faster and dynamic way to handle this filter feature using JSON()​


Let's make it easier using JSON!

Your collections have the same column names for this method.

The JSON()​ function:

Docs: JSON() PowerFx

Super useful for working with collections.  This function turns any collection into JSON.  

The ThisRecord​ operator:

Docs: ThisRecord PowerFx

This is a way to reference a Record​ within the current Scope​.

Example: 

//FYI this is psuedo code.
// Collection of records
collection_1:
[
    {ID:2,One: "a", Two: "b"},
    {ID:3,One: "a", Two: "b"},
    {ID:4,One: "a", Two: "b"},
    {ID:5,One: "a", Two: "b"},
    {ID:6,One: "a", Two: "b"}
]

// Iterate through each record and print as JSON
ForAll(collection_, print(JSON(ThisRecord)))

// Expected Output:
{"ID":2,"One":"a","Two":"b"} -- first line of output
{"ID":3,"One":"a","Two":"b"} -- second line of output
{"ID":4,"One":"a","Two":"b"} -- third line of output
{"ID":5,"One":"a","Two":"b"} -- fourth line of output
{"ID":6,"One":"a","Two":"b"} -- fifth line of output

So the Scope​ in this psuedo code is all Records​ stored in collection_1​. 

The code that compares JSON:

Comparing using JSON

//Create the collections
ClearCollect(
    collection_1,
    {
        ID: 2,
        One: "a",
        Two: "b"
    },
    {
        ID: 3,
        One: "a",
        Two: "b"
    },
    {
        ID: 4,
        One: "a",
        Two: "b"
    },
    {
        ID: 5,
        One: "a",
        Two: "b"
    },
    {
        ID: 6,
        One: "a",
        Two: "b"
    }
);
ClearCollect(
    collection_2,
    {
        ID: 2,
        One: "a",
        Two: "b"
    },
    {
        ID: 35,
        One: "a",
        Two: "b"
    },
    {
        ID: 3,
        One: "x",
        Two: "b"
    },
    {
        ID: 4,
        One: "x",
        Two: "b"
    },
    {
        ID: 5,
        One: "a",
        Two: ""
    },
    {
        ID: 6,
        One: "a",
        Two: "b"
    }
);
//Add a column that will contain all the data of each record in JSON format
ClearCollect(
    collection_1_with_concat_col_using_json,
    AddColumns(
        collection_1,
        concatenate_1_json,
        JSON(ThisRecord)
    )
);
ClearCollect(
    collection_2_with_concat_col_using_json,
    AddColumns(
        collection_2,
        concatenate_2_json,
        JSON(ThisRecord)
    )
);
//This finds items that match
ClearCollect(
    collection_compared,
    Filter(
        collection_2_with_concat_col_using_json,
        concatenate_2_json in collection_1_with_concat_col_using_json.concatenate_1_json
    )
);
//Adding the Not() fx will give the opposite result
//ClearCollect(collection_compared,Filter(collection_2_with_concat_col_using_json, Not(concatenate_2_json in collection_1_with_concat_col_using_json.concatenate_1_json)))



Sign in to leave a comment
Read Next
Extra Attributes - Autofill - KeePassXC