web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps - Building Power Apps / Trying to get AddColum...
Power Apps - Building Power Apps
Answered

Trying to get AddColumns to work with my dataset

(1) ShareShare
ReportReport
Posted on by bobgodin 380
@WarrenBelz I have the following code on the Items property of my Gallery:
If(
    varUsageClaims = true,
    Filter(
        Claims,
        Title = Gallery3_1.Selected.authorisation
    ),
    Sort(
        With(
            {
                _preFilter: Filter(
                    AddColumns(Claims,wma,LookUp(Events As _events,_events.theeventID=eventID).wma.Value),
                    startdatetime >= (DatePickerFrom_1.SelectedDate) && (enddatetime <= DatePickerTo_1.SelectedDate) && (StartsWith(
                        Title,
                        TextInput7.Text
                    ) || StartsWith(
                        licensee,
                        TextInput7.Text
                    ))&& wma=ComboBox4.Selected.Value
                )
            },
            If(
                !IsBlank(Searchbox.Selected.Result),
                Filter(
                    _preFilter,
                    (eventID in Searchbox.SelectedItems.Result)
                ),
                _preFilter
            )
        ),
        dateentered,
        SortOrder.Descending
    )
)
there's no errors showing up and the Gallery opens (eventually...) but in the middle of the code I am trying to get the wma field value from the Events list to add to the Claims dataset (the eventID field is common to both lists) - it doesn't seem to be working because when I insert a Label in the Gallery and set the properties to ThisItem.wma it isn't recognised - I know I am missing something really obvious but I can't see what it is.....
 
also the Gallery is very very slow to open
can anyone help please?
Categories:
Using Apps Using Formulas
I have the same question (0)
  • Suggested answer
    jpespena Profile Picture
    jpespena 335 on at
    Trying to get AddColumns to work with my dataset
    Hi,
     
    Try this: 
    AddColumns(
If(
    varUsageClaims = true,
    Filter(
        Claims,
        Title = Gallery3_1.Selected.authorisation
    ),
    Sort(
        With(
            {
                _preFilter: Filter(
                    Claims,
                    startdatetime >= (DatePickerFrom_1.SelectedDate) && (enddatetime <= DatePickerTo_1.SelectedDate) && (StartsWith(
                        Title,
                        TextInput7.Text
                    ) || StartsWith(
                        licensee,
                        TextInput7.Text
                    ))&& wma=ComboBox4.Selected.Value
                )
            },
            If(
                !IsBlank(Searchbox.Selected.Result),
                Filter(
                    _preFilter,
                    (eventID in Searchbox.SelectedItems.Result)
                ),
                _preFilter
            )
        ),
        dateentered,
        SortOrder.Descending
    )
),
wma,LookUp(Events As _events,_events.theeventID=eventID).wma.Value)
    Here I just move the AddColumns() function outside so it applies to all the filtering and sorting.
     

    If my post helped resolve your issue, please click Accept as Solution—this helps others find it more easily and marks the item as closed. If you found this or my previous reply helpful, a Like would also be appreciated!

  • WarrenBelz Profile Picture
    WarrenBelz 151,936 Most Valuable Professional on at
    Trying to get AddColumns to work with my dataset
    Hi @bobgodin​​​​​​​
    Firstly, AddColumns is a "local" operation, so it is best to keep it out of your initial Delegable filter. You also need to name the added column something different from the existing one.
    The other observation is you have a Combo Box with the output of .Result (I assume a Distinct filter). This is a legacy value and you are better just doing a straight Distinct (without the ForAll) with an output of .Value
    If(
       varUsageClaims,
       Filter(
          Claims,
          Title = Gallery3_1.Selected.authorisation
       ),
       With(
          {
             _preFilter: 
            Filter(
                Claims,
                startdatetime >= DatePickerFrom_1.SelectedDate && enddatetime <= DatePickerTo_1.SelectedDate && 
                (
                   StartsWith(
                      Title,
                      TextInput7.Text
                   ) || 
                   StartsWith(
                      licensee,
                      TextInput7.Text
                   )
                )
             )
          },
          Sort(
             Filter(
                AddColumns(
                   _preFilter,
                   wmaVal,
                   LookUp(
                      Events As _events,
                      _events.theeventID = eventID
                   ).wma.Value
                ),
                wmaVal = ComboBox4.Selected.Value && 
                (
                   Len(Searchbox.Selected.Result) = 0 || 
                   eventID in Searchbox.SelectedItems.Result
                )
             ),
             dateentered,
             SortOrder.Descending
          )
       )
    )
     
    Please click Does this answer your question 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 a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    Buy me a coffee
  • bobgodin Profile Picture
    bobgodin 380 on at
    Trying to get AddColumns to work with my dataset
    Hi Warren - thanks for that response however it has red line right through it - possibly brackets in wrong position - have tried to correct but failed so far - can you double check if possible?
     
    thank you!
  • WarrenBelz Profile Picture
    WarrenBelz 151,936 Most Valuable Professional on at
    Trying to get AddColumns to work with my dataset
    Hi @bobgodin​​​​​​​
    Where is the red line and what does the error message say. The structure looks correct to me, although I cannot see your data so do not know if the values are correct.
  • bobgodin Profile Picture
    bobgodin 380 on at
    Trying to get AddColumns to work with my dataset
    @WarrenBelz it starts on the With function and continues to the end of the code. At first it says Invalid no of arguments, received 4 expected 2 and then from the Filter statement it says Invalid no of arguments, received 4 expected 2 and Invalid no of arguments, received 1 expected 2-3
     
  • WarrenBelz Profile Picture
    WarrenBelz 151,936 Most Valuable Professional on at
    Trying to get AddColumns to work with my dataset
    Fixed on my post - see if you can spot it. Error messages were misleading.
  • Verified answer
    bobgodin Profile Picture
    bobgodin 380 on at
    Trying to get AddColumns to work with my dataset
    @WarrenBelz thanks Warren but I have a red squiggle over the last closing bracket and can't get rid of it if I add a bracket or subtract a bracket: 
    If(
   varUsageClaims,
   Filter(
      Claims,
      Title = Gallery3_1.Selected.authorisation
   ),
   With(
      {
         _preFilter: 
         Filter(
            Claims,
            startdatetime >= DatePickerFrom_1.SelectedDate && 
            enddatetime <= DatePickerTo_1.SelectedDate && 
            (
               StartsWith(
                  Title,
                  TextInput7.Text
               ) || 
               StartsWith(
                  licensee,
                  TextInput7.Text
               )
            )
         )
      },   
      Sort(
         Filter(
            AddColumns(
               _preFilter,
               wmaVal,
               LookUp(
                  Events As _events,
                  _events.theeventID = eventID
               ).wma.Value
            ),
            wmaVal = ComboBox4.Selected.Value &&
            (
               Len(Searchbox.Selected.Result) = 0 ||
               eventID in Searchbox.SelectedItems.Result
            )
         ),
         dateentered,
         SortOrder.Descending
      )
​​​​​​​   )
)
     
  • WarrenBelz Profile Picture
    WarrenBelz 151,936 Most Valuable Professional on at
    Trying to get AddColumns to work with my dataset
    You have some sort of strange hidden character string at the start of the second last line. If you delete the last two brackets (you need to backspace 7 or 8 times on the second last line) and then add the brackets in again, it should work.
  • bobgodin Profile Picture
    bobgodin 380 on at
    Trying to get AddColumns to work with my dataset
    @WarrenBelz - thanks Warren yes that fixed it - but I had to backspace about 12 times to get rid of "invisible" characters. One thing though - I have the wmaVal field added to my Gallery but it doesn't recognise ThisItem.wmaVal
  • WarrenBelz Profile Picture
    WarrenBelz 151,936 Most Valuable Professional on at
    Trying to get AddColumns to work with my dataset
    You may have to add it to both possible results (a Gallery needs to have a consistent output schema, no matter the filter)
    If(
   varUsageClaims,
   With(
      {
         _preFilter: 
         Filter(
            Claims,
            Title = Gallery3_1.Selected.authorisation
         )
      },
      AddColumns(
         _preFilter,
         wmaVal,
         LookUp(
            Events As _events,
            _events.theeventID = eventID
         ).wma.Value
      )
   ), 
   With(
      {
         _preFilter: 
         Filter(
            Claims,
            startdatetime >= DatePickerFrom_1.SelectedDate && 
            enddatetime <= DatePickerTo_1.SelectedDate && 
            (
               StartsWith(
                  Title,
                  TextInput7.Text
               ) || 
               StartsWith(
                  licensee,
                  TextInput7.Text
               )
            )
         )
      },   
      Sort(
         Filter(
            AddColumns(
               _preFilter,
               wmaVal,
               LookUp(
                  Events As _events,
                  _events.theeventID = eventID
               ).wma.Value
            ),
            wmaVal = ComboBox4.Selected.Value &&
            (
               Len(Searchbox.Selected.Result) = 0 ||
               eventID in Searchbox.SelectedItems.Result
            )
         ),
         dateentered,
         SortOrder.Descending
      )
   )
)
    Also note I am not currently getting Notifications of responses unless I am tagged.
     
    Please click Does this answer your question 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 a Like.
    MVP (Business Applications)    Visit my blog Practical Power Apps    Buy me a coffee

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
Coming soon: forum hierarchy changes
Explore Copilot Studio Forums, Blogs & Galleries!

Quick Links

Coming soon: forum hierarchy changes

In our never-ending quest to improve we are simplifying the forum hierarchy…

Chiara Carbone – Community Spotlight

We are honored to recognize Chiara Carbone as our Community Spotlight for November…

Congratulations to the October Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 651 Most Valuable Professional

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 385 Super User 2025 Season 2

#3
MS.Ragavendar Profile Picture

MS.Ragavendar 230 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps

Product updates

Microsoft Power Platform Community release plans