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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors