How to improve performance (250 columns x 30000 rows)

[Tom_Y]

Hi all,

I have 4 csv files from Sharepoint, each with 250 columns (same headers, Year 2021,2022,2023,2024), and altogether 30000 rows.

Currently I load it, and then either duplicate/ link that query quite a few times (around 20 times, different query) to reshape the data (e.g. Remove columns/ pivot/ unpivot/ combine with other fact table/ replace) for some key KPI for visuals.

It seems refreshing is becoming slower and slower. Is there any easy way to improve the peformance? 

Any website/ youtube video is very much welcomed. Thanks.

[christinepayton · Accepted solution]

Power BI likes tall sources, not wide sources. If you can get your year columns into a single column before landing them in the CSV, that would probably help a lot. Also do a "remove other columns" step first in your query to take out whatever you don't actually need/use in the data. 

You might move the query to a dataflow, too, and connect to that. Even if you just use that to make the connection, then do the transforms in the desktop, it will be much faster (there's "fat pipes" to the dataflow source, not so much to SharePoint if that's where your CSVs are). 

[Anonymous · Accepted solution]

Hi @Tom_Y ,

@christinepayton said is well, this is a way to improve performance.

You can optimize from the following aspects:

Perform as many calculations and conversion as possible in Power Query, instead of using DAX in Power Bi. This method uses query folding to significantly improve performance.

Avoid two -way and more relationships.

Select the correct storage mode (import, DirectQuery or dual storage) according to data aggregation requirements and visual requirements.

For more information about the storage mode, please refer to:

Use storage mode in Power BI Desktop - Power BI | Microsoft Learn

For more information about optimization, please refer to:

Optimization guide for Power BI - Power BI | Microsoft Learn

Power BI Best Practices - Improve report performance, security, and design (maqsoftware.com)

Speed/Performance aspects – (thebiccountant.com)

If you have other questions, please contact me at any time.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[christinepayton · Accepted solution]

Power BI likes tall sources, not wide sources. If you can get your year columns into a single column before landing them in the CSV, that would probably help a lot. Also do a "remove other columns" step first in your query to take out whatever you don't actually need/use in the data. 

You might move the query to a dataflow, too, and connect to that. Even if you just use that to make the connection, then do the transforms in the desktop, it will be much faster (there's "fat pipes" to the dataflow source, not so much to SharePoint if that's where your CSVs are).