Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 file at a different location, I can load all files from the folder. However, I can not use a file from that folder as a sample file. This problem occurs directly when trying to connect to the folder in Power Query. More details below.]

 

we use different data sources in our data model, including folders. This worked fine until today, when scheduled refresh returned that Power BI could not connect to data source "undefined" - a table was not loading correctly. I tried to track down the problem in PBI Desktop: Apparently several Queries are affected by this. All of them refer to one folder each (no two of them refer to the same folder), and all those folders are located inside the same folder (I'll call it "Data"). Queries that refer to Excel files located in "Data" and queries that refer to folders located elsewhere work fine, only these specific queries return an error. Inside Power Query, when I look at "Transform File from..." --> "Sample Query" --> "Sample File" --> "Source" for any of these folders, the following error is returned (the german expression in the beginning translates to "Operation is not valid due to the current state of the object."):


Microsoft.Mashup.Evaluator.Interface.ErrorException: Der Vorgang ist aufgrund des aktuellen Zustands des Objekts ungültig. ---> System.InvalidOperationException: Der Vorgang ist aufgrund des aktuellen Zustands des Objekts ungültig. ---> System.InvalidOperationException: Der Vorgang ist aufgrund des aktuellen Zustands des Objekts ungültig.
bei Microsoft.Mashup.Engine1.Library.File.FileHelper.GetAccessControlEntries(AccessControlEntriesCache cache, String fullName, Boolean directory, FileSystemRights rights)
bei Microsoft.Mashup.Engine1.Runtime.RecordValue.DemandRecordValue.get_Item(Int32 index)
bei Microsoft.Mashup.Evaluator.Services.ValueSerializer.<>c__DisplayClass68_0.<WriteMetaField>b__0()
bei Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
bei Microsoft.Mashup.Evaluator.Services.ValueSerializer.WriteMetaRecord(IRecordValue value, Boolean truncatedValue)
bei Microsoft.Mashup.Evaluator.Services.ValueSerializer.WriteValueEnd(IValue value, IRecordValue meta, Boolean truncated)
bei Microsoft.Mashup.Evaluator.Services.ValueSerializer.WriteLimitedValue(IValue value)
bei Microsoft.Mashup.Evaluator.Services.ValueSerializer.<>c__DisplayClass108_0.<WritePreviewFieldValue>b__0()
bei Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
bei Microsoft.Mashup.Evaluator.Services.ValueSerializer.<>c__DisplayClass99_2.<WritePreviewTable>b__2()
bei Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
bei Microsoft.Mashup.Evaluator.Services.ValueSerializer.<>c__DisplayClass99_1.<WritePreviewTable>b__1()
bei Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
bei Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
bei Microsoft.Mashup.Evaluator.Services.ValueSerializer.WritePreviewTable(IValue value)
bei Microsoft.Mashup.Evaluator.Services.ValueSerializer.HandleErrors(Action action, Action`1 handler)
bei Microsoft.Mashup.Evaluator.Services.ValueSerializer.WritePreviewValue(IValue value, Int32 serializeStructuredValueDepth)
bei Microsoft.Mashup.Evaluator.Services.ValueSerializer.SerializePreviewValue(IEngine engine, IValue value, Action`2 rowCountCallback, Nullable`1 options)
bei Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.SerializeRows(Int32 count)
bei Microsoft.Mashup.Evaluator.SimpleDocumentEvaluator.ValuePreviewValueSource.TableValuePreviewValueSource.get_SmallValue()
bei Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
bei Microsoft.Mashup.Evaluator.RemotePreviewValueSource.<>c__DisplayClass0_0.<RunStub>b__0()
bei Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
--- Ende der internen Ausnahmestapelüberwachung ---
bei Microsoft.Mashup.Evaluator.EvaluationHost.<>c__DisplayClass11_0.<TryReportException>b__1()
bei Microsoft.Mashup.Common.SafeExceptions.IgnoreSafeExceptions(IEngineHost host, IHostTrace trace, Action action)
bei Microsoft.Mashup.Evaluator.EvaluationHost.TryReportException(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Exception exception)
bei Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
bei Microsoft.Mashup.Evaluator.RemotePreviewValueSource.RunStub(IEngineHost engineHost, IMessageChannel channel, Func`1 getPreviewValueSource)
bei Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass12_1`1.<OnBeginGetResult>b__0()
bei Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)
bei Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetResult[T](IMessageChannel channel, BeginGetResultMessage message, Action`1 action)
bei Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.OnBeginGetPreviewValueSource(IMessageChannel channel, BeginGetPreviewValueSourceMessage message)
bei Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
bei Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
bei Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
bei Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
bei Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
bei Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
bei Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
bei Microsoft.Mashup.Evaluator.EvaluationHost.Run()
bei Microsoft.Mashup.Container.EvaluationContainerMain.Run(Object args)
bei Microsoft.Mashup.Evaluator.SafeThread2.<>c__DisplayClass9_0.<CreateAction>b__0(Object o)
bei Microsoft.Mashup.Container.EvaluationContainerMain.SafeRun(String[] args)
bei Microsoft.Mashup.Container.EvaluationContainerMain.Main(String[] args)
--- Ende der internen Ausnahmestapelüberwachung ---
bei Microsoft.Mashup.Evaluator.EvaluationHost.OnException(IEngineHost engineHost, IMessageChannel channel, ExceptionMessage message)
bei Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
bei Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
bei Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
bei Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
bei Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
bei Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
bei Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_SmallValue()
bei Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
bei Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)

 

 

Does anybody know how to solve this (or, as a first step, know why this error occurs)? Thanks a lot in advance!

8 REPLIES 8
Anonymous
Not applicable

I worked on this problem some more and found out that the problem only occurs when the sample file is being loaded from a folder located on a server. If I use a local file as sample file, everything works fine; the query can afterwards even pull data from the problematic folder, use the function that was created with the local file, and display correct results. To me this looks like a bug, even more since everything worked fine before the last update. Can somebody confirm this? Or do I miss something?

its happening to me also. If sample file is on server folder, it is no go.. if it is local, but data is in the same server folder, everything works fine.

 

It happens in Powerbi desktop and excel also..

 

Anonymous
Not applicable

@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!

pq-fromfolder-error.pngExcel 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?

@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
Not applicable

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.

Hi @Anonymous 

"he sample file is being loaded from a folder located on a server"

Do you mean this file is not in the same sever as the on-premise gateway?

If so, it can't refresh correctly because the data source and on-premise gateway should be in the same server.

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hello @v-juanli-msft,

 

thank you for trying to solve my problem. Unfortunately your answer does not help me since the files are located in the same server as the gateway. I do not think there is a problem there anyways, since I can't even load the specified data into PBI Desktop, so I guess the problem has nothing to do with the gateway.

 

To be more precise, I can't load files from these folders as Sample Data - as I mentioned before, if I copy one of these files to my local PC, use it as Sample Data to create the function for transforming data from its folder, and then use this function on the folder, I can load data from there.

 

What is more, if I try to rebuild the query, I can't select these folders as the source for sample data at all. When I try to, I get an error message saying the operation is not valid due to the current state of the object, i.e. the error message that is also shown in the existing query. However, single files (e.g. Excel files) from the exact same location do not cause any problems.

 

The question is, where is the difference between selecting a folder and selecting a file? And why does this not affect all folders, but only those at specific locations?

 

Regards

Moritz

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors