cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
buildbod Frequent Visitor
Frequent Visitor

Dataflow fails to return all SharePoint columns

I have a SharePoint list that has 54 columns. If I create a Dataflow using the SharePoint Online List Data Source to import the list it only returns 28 of the 54 columns. If I use Power BI Desktop it imports all 54 columns.

 

Is there a limit to the number of columns imported in a Dataflow?

 

Inspecting the code shows that the Dataflow is using a slightly different query:

 

Dataflow

 

let
  Source = SharePoint.Tables("https://tenant.sharepoint.com/sitename/"),
  Navigation = Source{[Name = "ListName"]}[Content]
in
  Navigation

 

PowerBI Desktop

 

let
Source = SharePoint.Tables("https://tenant.sharepoint.com/sitename/", [ApiVersion = 15]),
#"6785c373-1db7-4981-b904-e3f82afbef93" = Source{[Id="6785c373-1db7-4981-b904-e3f82afbef93"]}[Items],
#"Renamed Columns" = Table.RenameColumns(#"6785c373-1db7-4981-b904-e3f82afbef93",{{"ID", "ID.1"}})
in
#"Renamed Columns"

 

If I create a Blank Query in Dataflow and use the query from Power BI Desktop then all 54 columns are returned in the Preview. However once saved the Dataflow will not refresh.

 

Run timeDataflow nameDataflow refresh statusEntity nameStart timeEnd timeEntity refresh statusError
02/04/2019 10:52:44DemoFailedListName04/02/2019 10:52:4404/02/2019 10:52:45FailedError: Internal error Request ID: 586964a0-7d8b-d5cb-a312-3a0124f54ccc Activity ID: a6eb2825-ad91-4b8c-9772-149c0d0768cf

 

I've also noticed that the Run time is reported using US Date Time and Start/End as UK Data time

3 REPLIES 3
Community Support Team
Community Support Team

Re: Dataflow fails to return all SharePoint columns

@buildbod,

 

The data source is sharepoint online list, right? Please check if the credential is valid via dataset-> data source credentials.

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

buildbod Frequent Visitor
Frequent Visitor

Re: Dataflow fails to return all SharePoint columns

Thanks @v-yuta-msft . I think the credentials was a bit of a red herring bought about through some AD mischief that was happening at the time. 

 

The Dataflows are refreshing but erroring in Power BI online:

 

Something went wrong
There was a problem opening the dataflow editor.
Please try again later or contact support. If you contact support, please provide these details.
Activity ID: 91d8f8f2-bb85-4926-9113-c697933d0809
Request ID: 5cbb37f2-8dc9-92d3-7402-23ddcb4c0a52
Correlation ID: 7e960324-aa78-55f3-5485-8ce28f14eab3
Time: Tue Feb 05 2019 10:40:03 GMT+0000 (GMT Standard Time)
Version: 13.0.8234.157
Cluster URI: https://wabi-north-europe-redirect.analysis.windows.net

When I open the Dataflow in Power BI desktop all columns are present. This narrows the issue down to the Dataflow Preview and Power Query tool that, as mentioned in my initial post, fails to report the correct number of columns. As it fails to report all columns it is not possible to create a query that references a column outside of the 28 unless the query is created in Power BI Desktop and copied across. Even then you do not know if the query will be effective until you save, refresh and add the Dataflow into Power BI Desktop (as you cannot see the results in the Preview).

Highlighted
Galia Frequent Visitor
Frequent Visitor

Re: Dataflow fails to return all SharePoint columns

 

@buildbod,

 

Detaflow engoineering team looks into the two issues you reported (problem with preview and refresh failure), and we wll get back to you once we will understand and fix the root cause.

 

Regards,

Galia