Using .iqy file for import from SharePoint folder view

[Anonymous]

Hi,

I want to connect .iqy file (this file I get when clicking "Export to Excel" button in SharePoint Online) into PowerBi and enable background refresh and automatic refresh upon opening PowerBI for this .iqy connection. What modifications should I make to this code and what else I should do?

WEB
1
HiddenURL.dll?XMLDATA=1&List={ListID}&View=ViewID&RowLimit=0&RootFolder=undefined

Selection={HiddenID}-HiddenID
EditWebPage=
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
SharePointApplication=hiddenID
SharePointListView=hiddenID
SharePointListName={ListNameID}
RootFolder=undefined

[Anonymous]

@Anonymous,

You can follow the steps below. You can check Guo_au's reply in this similar thread.

1.Open the iqy file with Notepad and copy the URL address, which includes names of list and view.
2.Open Web connector in Power BI Desktop.
3.Paste the URL link in web connector and then choose your table to import.

After creating reports in Power BI Desktop, you can publish the report to Power BI Service, and set schedule refresh for the dataset.

Regards,
Lydia

[Anonymous]

Hi, @Anonymous I get the error " "We couldn't authenticate with the credentials provided" when entering following code: https://XX.sharepoint.com/teams/XX/_vti_bin/owssvr.dll?XMLDATA=1&List={LISTID}&View=CODEID  Is there something wrong with URL code I try to generate?

[Anonymous]

@Anonymous,

What authentication type do you use in Power BI Desktop? Do you use SharePoint Online or on-premises SharePoint?

Regards,
Lydia

[Anonymous]

Hi Lydia @Anonymous, it is SharePoint Online

[Anonymous]

@Anonymous,

What authentication type do you use in Power BI Desktop when connecting to the data source?

Regards,
Lydia

[Anonymous]

Hi Lydia @Anonymous I use Organizational account, also tried Basic and Windows. When trying to access same Site with Get data-SharePoint list credentials work fine, so I wonder whether problem is with iqy code.

[Anonymous]

@Anonymous,

Please use Fiddler to check if there are any details about the error.

Regards,
Lydia