Power BI M-QUERY

[ArvindJha]

Hello Team,

Data size is 32 MB , my logic loads while data size is 6 MB but keeps n loading forever with 32 MB data.

When i checked step by step it happens at grouping operation

#"Grouped Rows" = Table.Group( #"BufferTable", {"SUBID"}, {{"TRANID", each List.Max([TRANID]), type nullable number}})

To resolve the same i also introduced BufferTable before the above step

#"BufferTable" = Table.Buffer(previous_action)

still it seems to load forever.

any ways it can be optimized

Thanks

[v-xinruzhu-msft]

Hi,

Thanks for the solution danextian  and bhanu_gautam  offered, and i want to offer some more information for user to refer to.

hello @ArvindJha , based on your descriotion, Before grouping, you can reduce the size of your data, then filter out the required rows based on the conditions and remove the unnecessary columns. You can check the link, which provides some methods to optimize performance.

Optimization guide for Power BI - Power BI | Microsoft Learn

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ArvindJha]

thanks for the response , did the above steps have kept only 2 columns , removed duplicates and doing group by still very slow

[danextian]

I'm thinking your subid has quite a lot of distinct values.

Try:

• Remove all columns except TRANID and SUBID
• Sort it by TRANID in descending order
• Aapply Table.Buffer
• Remove duplicates on SUBID

[ArvindJha]

thanks for the response , tried the above steps still is slow , works fast with less data

[danextian]

This is when you'll want to consider moving the ETL upstream. You can use several dataflows and apply extra transformation in Power Query to the output of those dataflows. Just take note that, referencing an external query requires a computed entity which is a premium-only feature so no merging of queries within the same dataflow. Referencing another dataflow also requires premium.

[bhanu_gautam]

@ArvindJha , Try using 

let
Source = previous_action,
BufferTable = Table.Buffer(Source),
GroupedRows = Table.AggregateTableColumn(
BufferTable,
"SUBID",
{{"TRANID", List.Max, "Max TRANID"}}
)
in
GroupedRows