Folder query stuck updating forever

[gudnsouza]

Hello everyone,

In my company, we have a dashboard for HR analysis that have as one of it's data sources a folder query with ~400 excel files, with ~350 rows in each one. This number grows by 2 everyday.

Recently, the data source doesn't update anymore. It get's stuck for hours trying to work it out but it fails no matter what. Are there any way for me speed up the updating proccess in this case? I'm also open for ideas on how to build this data source better.

Thank you.

[pranit828]

Hi @gudnsouza 

I agree with @parry2k about keeping it in a database server rather than in excel files.

Still if you need to have the data in excel then it requiers out of the box thinking as the ask is intersting.

In this case I would write a powershell script to append all the files form specified or different folders together into one(Considering all the excel files have similar column pattern, 400 files must have some consistency) and then use this file to as a datasource in Power BI.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

Regards,
Pranit

[parry2k]

@pranit828, not a bad idea to club these together before consuming in power bi but why to add another step in the process if there is a possibility to store the data in first place in sql server. I hate one date move from 1 place to another and then another and it becomes a nightmare to manage when things fail.

Let's do it right but your idea is not bad but I will not do it.

[parry2k]

@gudnsouza not sure if it is a scalable solution, not sure what is the source of these excel files, may be easier to store the data in sql server (onprem or on azure sql server) and use that in Power BI instead of directly reading Excel files, it is not going to work if you are adding 2 files every day

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡