0

I am new to Powerapps and I have noticed that the Distinct function returns a table of the distinct values(only returns the distinct column not the full row). Is there a way to filter a table so that it returns back a subset of the full table with distinct values in a specified column.

Jonathan
  • 441
  • 1
  • 9
  • 28

1 Answers1

1

You can use the GroupBy function for this. Take a look at the documentation, or in the example below:

Assuming that cities is a table with the following values:

City Country Population
London UK 8615000
Berlin Germany 3562000
Madrid Spain 3165000
Rome Italy 2874000
Paris France 2273000
Hamburg Germany 1760000
Barcelona Spain 1602000
Munich Germany 1494000
Milan Italy 1344000

The expression GroupBy(cities, "Country", "Cities") will return a table with a column "Country", and a column called "Cities" whose value will be a table with all cities for that country.

You can then use functions such as AddColumns and Sum to aggregate the values of the inner table, like in the example below:

AddColumns(
    GroupBy(cities, "Country", "Cities"),
    "Sum of City Populations",
    Sum(Cities, Population))

In your tweets example, if you want to get one tweet from each day, you can have an expression like the one below:

AddColumns(
    GroupBy(Tweets, "crf1d_date_index", "Dates"),
    "SampleTweet",
    First(Dates))

Where it would have a new column with the first tweet from each date. Or if you want a single field from the group, you can have something like this:

AddColumns(
    GroupBy(Tweets, "crf1d_date_index", "Dates"),
    "FirstTweetTime",
    First(Dates).tweet_time)
carlosfigueira
  • 85,035
  • 14
  • 131
  • 171
  • Thank you again @carlosfigueira. I have looked into using groupby and ungroup as well but when I use this formula ``` Set(distinctTweets, AddColumns( GroupBy(Tweets, "crf1d_date_index", "Dates"), "First date in index", First(Dates))); Set(distinctTweets2, Ungroup(distinctTweets, "First date in index"));``` I still get a table in each cell. – Jonathan May 11 '22 at 01:03
  • Can you clarify what you are trying to do? Get the distinct tweets by a specific day? Get one tweet from each day? Something else? – carlosfigueira May 11 '22 at 03:35
  • I am trying to create a screen where users can choose what day they would like to see tweets about (the days available are based on the data)… it is the same issue we were discussing before (https://stackoverflow.com/questions/72145051/how-to-get-record-before-and-after-in-forall-loop-powerapps/72163430#72163430) however I noticed that the if statements are not getting executed properly. So, I would like to filter the table to get one tweet per day then create the step based on that. – Jonathan May 11 '22 at 04:38
  • 1
    You can use something like the First function to get one tweet from each day. I've updated the answer with an example. – carlosfigueira May 11 '22 at 14:46