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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Jun_Wang
Advocate I
Advocate I

optimise power query codes

Hi there,

 

It'd highly appreciated if some one can help optimise power query codes.

 

I have a very big table with more than 500 columns along with nested tables. I used to use table.selectRows and table.selectColumns together to stop importing unnecessary columns and rows.

Table.SelectRows(Table.SelectColumns(Source{[Name="orders",Signature="table"]}[Data],{"unit_value", "id", "name"}), each ([unit_value] = "e0071b661681"))

 

Now I need to expand one more column (named as name) from nested table (named as referenceOrderID). The above combination codes don't work on nested tables. It needs to expand the column first and do table.selectRows and table.selectColumn. However it takes a lot of time to run the query.

 

Is there a way to optimise these two steps?

 

Great thanks,

Jun

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @Jun_Wang 

 

Try this

= Table.TransformColumns(#"Grouped Rows",{"All Data Table", each Table.SelectRows(_, each [Period ID]=20210101)})

vjingzhang_0-1648698066196.png

= Table.TransformColumns(#"Grouped Rows",{"All Data Table", each Table.SelectColumns(_, {"Employee ID","Salary"})})

vjingzhang_1-1648698257565.png

 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it.

v-jingzhang
Community Support
Community Support

Hi @Jun_Wang 

 

I recommend that you separate Table.SelectColumns and Table.SelectRows into two separate steps. This may improve the performance than combining them in a single step. 

 

It is possible to filter a nested table without expanding it. I will provide a sample later but I'm not sure whether it will improve the performance. 

 

Best Regards,
Community Support Team _ Jing

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.