optimise power query codes

[Jun_Wang]

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

[v-jingzhang]

Hi @Jun_Wang 

Try this

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

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

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

[v-jingzhang]

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