Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
asif999
Frequent Visitor

Table.SelectRows Custom Column Looping Variable

I am new to Power Query.

 

I got this solution from another forum. I want to understand the logic behind this function.

 

asif999_0-1680181510652.png

 

 

Funtion used = Table.AddColumn(Source, "Custom", each Table.SelectRows(Source,(x)=>x[Order ID]=[Order ID])) (highlighted part)

 

The above functions fell into the error when I tweaked it from this condition (x)=>x[Order ID]=[Order ID] to [Order ID]=[Order ID].

 

 

 

Download File

1 ACCEPTED SOLUTION
AlienSx
Super User
Super User

Hi, @asif999 it's all about misleading "each" replacing (_) => and omitting _ later. Makes sense to use this in simple expressions but when you are dealing with iterator inside another iterator ... it's easy to get lost. Rewrite your step as below. Table.AddColumn iterates Source and (w) is a current row of that iteration. But w[Order ID] is another table you are iterating. (x) is a current item (row) of that iteration (over w[Order ID] table)  

 

Table.AddColumn(Source, "Custom", (w) => Table.SelectRows(Source,(x) => x[Order ID]= w[Order ID]))

 

View solution in original post

3 REPLIES 3
AlienSx
Super User
Super User

Hi, @asif999 it's all about misleading "each" replacing (_) => and omitting _ later. Makes sense to use this in simple expressions but when you are dealing with iterator inside another iterator ... it's easy to get lost. Rewrite your step as below. Table.AddColumn iterates Source and (w) is a current row of that iteration. But w[Order ID] is another table you are iterating. (x) is a current item (row) of that iteration (over w[Order ID] table)  

 

Table.AddColumn(Source, "Custom", (w) => Table.SelectRows(Source,(x) => x[Order ID]= w[Order ID]))

 

@asif999 many thanks for accepting my answer as a solution... but I would like to reconsider my explanation. 

Table.AddColumns still iterates Source table row by row. "w" is still a variable referring to the current row of iteration. But at any particular row of iteration "w" calls for another iteration function - Table.SelectRows. The last one asks for a table to iterate on - the original Source table -  and iteratates row by row with (x), selects those records of Source with [Order ID] = currently iterated row (w[Order ID]). It all looks like Table.Group by [Order ID] but it's not. It must be painfully slow. 

Thanks for the explanation.

 

Now I got it 100%.

 

I was doubting the Table.SelectRows' iterator (x). And I was about to ask you the same.

 

I appreciate you have reconsidered your answer. Thanks once again😊

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors