Problems with updating DATA from folder - HELP!!!!

[jr10]

Hello powerbiers, i am looking for some help.

I made a dashboard where i get the information from a folder, the folder contains around 15 .mdb files. First time it worked fine with no problem, now i have to delete some .mdb files and placed new ones, it would be like this:

First time : folder xxx : 230101.mdb; 230201. mdb; 230401.mdb, ......; 231501.mdb

Update: folder xxx: 231001.mdb; 231101. mdb; 231201.mdb, ......; 233001.mdb

and on and on every week, deleting old files and dumping new .mdb files into the same folder. Always the same kind of file with the same tables and columns.

The thing is that when i tried the first update replacing some .mdb i get the next errors:

OLE DB or ODBC error: [DataFormat.Error] Couldnt find file '231023.ldb

OLE DB or ODBC error: [DataFormat.Error] Does not recognize the format of the database 'PO231023.ldb

its like instead of taking the .mdb it tries to read the .idb file, when all the files in the folder are .mdb,

And the weird thing for me is that when i go to transform the data the power bi and the query works perfectly, but when i try to close and apply changes i get all kind of errors.

any idea what i should do?

Thanks a lot.

[Greg_Deckler]

@jr10 Can you paste the relavent Power Query M code from Advanced Editor so we can see what is going on? Did you use a Folder query?

[jr10]

Hey @Greg_Deckler , thnks for the fast response, this are the steps:

= Folder.Files("C:\Users\xxxx\folder")
= Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true)
= Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content]))
= Table.RenameColumns(#"Invoke custom function1", {"Name", "Source.Name"})
= Table.SelectColumns(#"Columns with name changed1", {"Source.Name", "Transform File"})
= Table.ExpandTableColumn(#"Other Columns Removed1", "Transform File", Table.ColumnNames(#"Transform File"(#"Example File")))
= Table.TransformColumnTypes(#"Expanded table column1",{{"Source.Name", type text}, {"GROUP", type text}, {"TIME", Int64.Type}, {"ARL", Int64 .Type}, {"OPE_STATUS", type text}, {"PI_SOTR", Int64.Type}, {"ARL_ECON", Int64.Type}, {"EST_REMUN", type text}, {"ENERGIA", type number} , {"NODE_PRICE", Int64.Type}, {"PR_REM_ENERGIA", type number}, {"SMEC", type text}, {"POT_DISP", type number}, {"POT_OPE_CPO", type number}, {"SPOT_BUY ", type number}, {"SCTD", type number}, {"SCO", type number}, {"PIND", type number}, {"PINDPROG", type number}, {"PINDFORZ", Int64.Type }, {"POT_DISP_GAS", type number}, {"POT_DISP_RESERVA", type any}, {"NOMINATED_GAS", type any}, {"COST_406", type number}, {"REM_ADDICIONAL", Int64.Type}, {" DESP_ECON", type text}, {"CCM", type any}, {"CFO", type any}, {"CGN", Int64.Type}, {"CGO", Int64.Type}, {"POT_DISP_R19", type number}, {"FACTOR_KM", Int64.Type}, {"REPRESENTATIVE_TIME", type text}, {"ENERG_GEN_TRANSAC", type number}, {"ENERG_OPE_TRANSAC", type number}, {"OVERC_FORZ_OWN", type any}, {"PGENE_RES354", type any}, {"CGU_OWN", type any}, {"CGU_MEM", type any}, {"CGU_GAS_CEDIDO", type any}})
= Table.ReplaceValue(#"Type changed",".mdb","",Replacer.ReplaceText,{"Source.Name"})
= Table.ReplaceValue(#"Replaced Value","PO","",Replacer.ReplaceText,{"Source.Name"})
= Table.AddColumn(#"Replaced value1", "Date", each Date.FromText([Source.Name],[Format = "yyMMdd"]))

 For me its weird cause the information really loads in the power query editor, i´ve read that it could be a relationship thing but i have delete al the relationships and every aditional table and just wont work, i´´ve tried clearing the cache as well,

thanks for any help