Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Error when loading data from certain folders

Hello everybody,   [Edit 09-12-2019: To cut it short, I want to load data from a certain folder in Power Query. I can 1) load an Excel file from that folder and I can also 2) when having a sample f...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

AgencyPowerBi(or anybody else who knows what's going on here): Did you happen to find a solution by now, or the reason for the error to occur? I'm still clueless about this one. I was able to relocate the data I was trying to load back then to a different folder, but now I want to load some Excel files that I don't manage myself, so I need to pull them from a folder that causes this error. Any help appreciated!

vJuricek's avatar
vJuricek
Frequent Visitor
6 years ago

Excel 365
PQ > Get Data > From File > From Folder
PBID

Get Data > File > Folder

Unexpected Error still occurs in both Excel 365 and PBID when folder is located on the network.

Is it possible that noone from Microsoft noticed this issue in half a year?

  • vJuricek's avatar
    vJuricek
    Frequent Visitor
    6 years ago

    Anonymous I have a solution! In PBID you can run Python Script. So, this is mini guide:

    • open PBID
    • Get Data > More > Other > Python script
    • type
    import pandas as pd
import glob # Unix style pathname pattern expansion
path = '\\\\diskstationhp\\Pripravy\\Príklady-Excel\\AdventureWorks2005\\Export\\ProductByYear'
files = glob.glob(path + "**/*.txt",recursive=True)
df = pd.concat([pd.read_csv(f, sep=';',quotechar='"',encoding='CP1250').assign(filename=f.split("\\")[-1]) for f in files])
    • click OK
    • in Navigator window select df table and choose Load or Transform and continue as usual

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Awesome, thanks for sharing! I'll look into this when I have some time to spare as I haven't used Python Script before and this topic is not that much pressing anymore (as you might have guessed from my last comment's date). This might still come in handy sometimes.