cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Help understanding some working code

I received the following code from @v-xida-msft to make a label visible using the following logic:

 

Let's say we have 3 records:

 

Country      Main Industry          Want to visit?

A                 Wool                           Yes

B                   Steel                           No

C                   Steel                          Yes

 

I want to display a label if the following conditions are met:

-The main industry for 1 record matches another record

-Of the matching 2 records ONLY, the values for 'want to visit'? are different. 

 

Here's the code: 

 

If(
   Sum(
       ForAll(
              RenameColumns('DataSource', "Main Industry", "Main Industry1", "Want to visit?", "Want to visit?1"),
             If(
                 !IsBlank(LookUp('DataSource', 'Main Industry' = 'Main Industry1' && 'Want to visit?' <> 'Want to visit?1')) && CountRows(Filter('DataSource', 'Main Industry' = 'Main Industry1'))=2,
                 1,
                 0
            )
       
       ),
       Value
   ) >=2,
   true,
   false
)

 

The code works fine-thanks @v-xida-msft for the help 🙂  ; I just want to understand it a bit better. 

 

I believe that the column names are renamed 'xx' => 'xx1' and then compared with each other. So 'xx' is compared with 'xx1' etc. 

 

But if this is correct, then I am struggling to understand how this works as a logical test. Surely a copied column will always, by definition, contain the same values as the original column? 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Anonymous 

I think its great that you're seeking clarification and I'm sure this thread will be useful to many others who are trying to understand ForAll and RenameColumns.

 

I suspect that cause of this confusion is that the RenameColumns function returns a *copy* of DataSource with the columns renamed. The original DataSource remains the same.

Within the formula, we therefore deal with two distinct data sources.

 

This is the dataset that we loop over with the call to ForAll.

 

Country      Main Industry1          Want to visit?1

A                 Wool                         Yes

B                  Steel                         No

C                  Steel                         Yes


Let's imagine we're stepping over the code within the ForAll loop and we've reached the 3rd iteration (eg, we're operating on row C of the above data source)

With this particular row, the if condition resolves to this:

If(!IsBlank(LookUp('DataSource', 'Main Industry' = 'Steel' && 'Want to visit?' <> 'Yes'))


The call to the LookUp function will operate against the original datasource, shown here.

 

Country      Main Industry          Want to visit?

A                 Wool                         Yes

B                  Steel                         No

C                  Steel                         Yes

 

This call will match row B, because 'Main Industry' = 'Steel' and 'Want to visit?' <> 'Yes'.

Hopefully, this adds some more clarity but let us know if you're still stuck, and I'm sure one of us will be able to explain.

View solution in original post

7 REPLIES 7
Super User III
Super User III

@Anonymous 

You are correct that the new column will have exactly the same values as the original column.

I believe the original column was duplicated because a LookUp cannot be performed on itself (example 'Main Industry'='Main Industry' would return the incorrect result).

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

 

 

Yes, @mdevaney is correct.

Without a call to the RenameColumns function, we would need to specify the table name in the call to LookUp and apply the 'disambiguation' operator. There are more details about this operator in the help page here.

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/operators 

Calling RenameColumns rather than disambiguating can make formulas a bit more readable for some people.

@timl
Yes, that’s true. Renaming does make the code more readable. Thanks for pointing that out! In my opinion readability of code is more important than brevity and is right up there with performance.
Community Support
Community Support

Hi @Anonymous ,

Yes, this is mainly for avoiding disambiguation, If two column names are the same, Lookup function and Filter function are not able to identify correctly.

Renaming a column name is a common method when looping through a data source through ForAll function.

Sik

Anonymous
Not applicable

@mdevaney  @Tim1 @v-siky-msft  Thanks for your help. That confirms my presumption that the duplicated columns will be identical in their content to the original set of columns. I now understand that the RenameColumns() function is a way to make this readable, as we don't need to create a new table/reference the old table, but simply work with the existing table. 

 

But...if this is the case, then I remain confused about the following statement:

 

 If(
                 !IsBlank(LookUp('DataSource', 'Main Industry' = 'Main Industry1' && 'Want to visit?' <> 'Want to visit?1'))

 

The new columns are identical to the old ones! So surely, the 'Want to visit?' column will always be the same as the 'Want to visit?1' column, and the if() statement will always evaluate as false? 

 

I know for a fact that this is not the case, because the code is working as intended; if two items from 'main industry' are the same, and for these two records, 'want to visit' contains different values, then the statement evaluates to true.

 

So how does this work? 

 

Thanks again for the help so far 🙂

 

Hi @Anonymous 

I think its great that you're seeking clarification and I'm sure this thread will be useful to many others who are trying to understand ForAll and RenameColumns.

 

I suspect that cause of this confusion is that the RenameColumns function returns a *copy* of DataSource with the columns renamed. The original DataSource remains the same.

Within the formula, we therefore deal with two distinct data sources.

 

This is the dataset that we loop over with the call to ForAll.

 

Country      Main Industry1          Want to visit?1

A                 Wool                         Yes

B                  Steel                         No

C                  Steel                         Yes


Let's imagine we're stepping over the code within the ForAll loop and we've reached the 3rd iteration (eg, we're operating on row C of the above data source)

With this particular row, the if condition resolves to this:

If(!IsBlank(LookUp('DataSource', 'Main Industry' = 'Steel' && 'Want to visit?' <> 'Yes'))


The call to the LookUp function will operate against the original datasource, shown here.

 

Country      Main Industry          Want to visit?

A                 Wool                         Yes

B                  Steel                         No

C                  Steel                         Yes

 

This call will match row B, because 'Main Industry' = 'Steel' and 'Want to visit?' <> 'Yes'.

Hopefully, this adds some more clarity but let us know if you're still stuck, and I'm sure one of us will be able to explain.

View solution in original post

Anonymous
Not applicable

@timl Perfect-makes sense! I didn't realise that it was the records that were being looped through. My lack of understanding of the forall() function is to blame!

 

Much appreciated-thank you to everyone who assisted here.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,973)