question

ccroche avatar image
0 Votes"
ccroche asked emizhang-msft commented

Power Query in Excel different from Power Query in Power BI

Hello everyone! I mainly use Power Query in Power BI and recently in Excel. I noticed some differences between these two "versions" of Power Query (in the M language ,for instance). Recently, I noticed a blocking difference: in PQry for PBI I can access all the files of a SharePoint site library. If I do the same operation with PQry in Excel, some folders are invisible and inaccessible. Have you ever had this experience? Do you know the cause and solution to this weirdness? Thanks for your help!

power-query-not-supportedoffice-excel-itpro
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ccroche,
I am just writing to see if you have obtained the opportunity to test the solution. If anything is unclear with the previous information I've provided to you, please don't hesitate to let me know. I am glad to be of assistance.

0 Votes 0 ·
MattAllington-5456 avatar image
0 Votes"
MattAllington-5456 answered ccroche commented

You should not post the same question on 2 different forums. It is disrespectful to those who answer questions, only to find out that you have already got the answer somewhere else. 

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Dear @MattAllington-5456, sorry for that but I can’t remove my other post nor can I even reply to it! I don’t know why… Hopefully I posted my question here too, because I found someone replying to it here and not in the really appropriate forum. I wanted to add a link to this question in the other forum but haven’t been able to do it!


0 Votes 0 ·
emizhang-msft avatar image
0 Votes"
emizhang-msft answered

Hi @ccroche,
Did you need any permissions to access these files in SharePoint?
Did all kinds of files cannot be load in Excel?
I suggest you read this article and confirm if it is helpful:
https://docs.microsoft.com/en-us/power-query/connectors/sharepointfolder
Please be a bit more precise to explain your problem so that I can get more accurate solutions to this problem. I’m glad to help and follow up your reply.


If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ccroche avatar image
0 Votes"
ccroche answered emizhang-msft commented

Dear @emizhang-msft, I have the owner permissions on the SharePoint website I’m pointing at. All types of file are visible to me and I can access them in the SharePoint. I want to access a specific file in a given folder and select the last version of it by its "last modified date" property. This is the process that I follow (the screenshots are in French but you’ll certainly be able to see what it’ about). I do this at the same time in Power Query for Excel and in Power Query for Power BI:
- I choose a connexion to a SharePoint folder:
80044-image.png
- I then navigate to this folder:
79987-image.png
- I connect to the site with my professional credentials and select the upmost level to apply the settings
80015-image.png
- I’m then presented with the list of files (at least, a part of them):
80102-image.png
- I choose to modify this list in order to select the ONE file I’m interested in:
I filter the list by folder starting with…:
80112-image.png
- I then get an empty list in Pwr Qry for Excel whereas I get a correct list of the files I need in Pwr Qry for PBI:
In excel: 80112-image.png
In PBI: 80121-image.png

Is there any explanation to this behavior? I noticed that the behavior has slightly changed from the previous time I did it: in Excel, I could see some sub-folders of the folder "Médical". Today, I can’t see them anymore.

Thanks for your help!


image.png (42.4 KiB)
image.png (9.5 KiB)
image.png (20.3 KiB)
image.png (101.9 KiB)
image.png (13.1 KiB)
image.png (34.1 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Did the sign in account in third picture as the same as the Office login Account?

0 Votes 0 ·
ccroche avatar image
0 Votes"
ccroche answered ccroche published

I may have found something indicating why I can’t find the same thing in Excel. In the Query options / Account, I can’t connect to my account and get this error:
80329-2021-03-22-19-51-38.png
I don’t understand what this means but it may be a track to find the solution!
The details of the error shows:
Feedback Type:
Frown (Error)

Error Message:
Nous ne pouvons pas contacter le service. Cela peut être dû à votre connexion réseau, ou le service n'est peut-être pas disponible. Si le problème persiste, contactez votre administrateur.

Stack Trace:
à System.Environment.GetStackTrace(Exception e, Boolean needFileInfo)
à System.Environment.get_StackTrace()
à Microsoft.Mashup.Client.UI.Shared.StackTraceInfo.GetCurrent()
à Microsoft.Mashup.Client.UI.Shared.HtmlControls.Options.AccountPaneHtmlControl.OnClientConfigPollerTimeout(Object sender, EventArgs e)
à Microsoft.Mashup.Client.UI.Shared.ClientConfigPoller.<>c_DisplayClass2.<OnTimeoutWrapper>b0()
à Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
à Microsoft.Mashup.Client.UI.Shared.ClientConfigPoller.OnTimeoutWrapper(Object sender, EventArgs e)
à System.Windows.Forms.Timer.OnTick(EventArgs e)
à System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
à System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
à System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
à System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
à System.Windows.Forms.Application.ThreadContext.LocalModalMessageLoop(Form form)
à System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
à System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
à System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
à Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialog.<>n
FabricatedMethod8(IWindowHandle )
à Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
à Microsoft.Mashup.Client.Excel.FloatingDialogs.OptionsDialog.Show(IWindowHandle owner, IUIHost uiHost, ApplicationTracingHost applicationTracingHost, Queries queries, OptionsDialogInfo dialogInfo, Boolean applicationAllowsUpdate, Boolean applicationSupportsDataModel, Boolean isDataCatalogEnabledByFederationEnv, Boolean canEditFile)
à Microsoft.Mashup.Client.Excel.DialogManager.ShowSettingsDialog(IWindowHandle ownerWindow, IWorkbook workbook)
à Microsoft.Mashup.Client.Excel.Shim.IWindowContextExtensions.TryInvokeOnParentWorkbook(IWindowContext windowContext, Action`1 action)
à Microsoft.Mashup.Client.Excel.ExcelRibbonHandler.OnOptionsAction(IWindowContext windowContext)
à Microsoft.Mashup.Client.Excel.NativeRibbonHandler.<>c
DisplayClass1.<OnRibbonButtonAction>b_0()
à Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
à Microsoft.Mashup.Client.Excel.ExcelCallbackManager.InvokeAndReturnHResult(Action action)

Invocation Stack Trace:
à Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
à Microsoft.Mashup.Client.UI.Shared.StackTraceInfo.GetCurrent()
à Microsoft.Mashup.Client.UI.Shared.HtmlControls.Options.AccountPaneHtmlControl.OnClientConfigPollerTimeout(Object sender, EventArgs e)
à Microsoft.Mashup.Client.UI.Shared.ClientConfigPoller.<>c_DisplayClass2.<OnTimeoutWrapper>b0()
à Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
à Microsoft.Mashup.Client.UI.Shared.ClientConfigPoller.OnTimeoutWrapper(Object sender, EventArgs e)
à System.Windows.Forms.Timer.OnTick(EventArgs e)
à System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
à System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
à System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
à System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
à System.Windows.Forms.Application.ThreadContext.LocalModalMessageLoop(Form form)
à System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
à System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
à System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
à Microsoft.Mashup.Client.UI.Shared.WebDialogs.WebDialog.<>n
FabricatedMethod8(IWindowHandle )
à Microsoft.Mashup.Client.UI.Shared.WindowManager.ShowModal[T](T dialog, Func`1 showModalFunction)
à Microsoft.Mashup.Client.Excel.FloatingDialogs.OptionsDialog.Show(IWindowHandle owner, IUIHost uiHost, ApplicationTracingHost applicationTracingHost, Queries queries, OptionsDialogInfo dialogInfo, Boolean applicationAllowsUpdate, Boolean applicationSupportsDataModel, Boolean isDataCatalogEnabledByFederationEnv, Boolean canEditFile)
à Microsoft.Mashup.Client.Excel.DialogManager.ShowSettingsDialog(IWindowHandle ownerWindow, IWorkbook workbook)
à Microsoft.Mashup.Client.Excel.Shim.IWindowContextExtensions.TryInvokeOnParentWorkbook(IWindowContext windowContext, Action`1 action)
à Microsoft.Mashup.Client.Excel.ExcelRibbonHandler.OnOptionsAction(IWindowContext windowContext)
à Microsoft.Mashup.Client.Excel.NativeRibbonHandler.<>c
DisplayClass1.<OnRibbonButtonAction>b_0()
à Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
à Microsoft.Mashup.Client.Excel.ExcelCallbackManager.InvokeAndReturnHResult(Action action)


Supports Premium Content:
True


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.