Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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.
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