Dataflow fails to return all SharePoint columns

[buildbod]

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 time	Dataflow name	Dataflow refresh status	Entity name	Start time	End time	Entity refresh status	Error
02/04/2019 10:52:44	Demo	Failed	ListName	04/02/2019 10:52:44	04/02/2019 10:52:45	Failed	Error: 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

[qsong]

Hi, Do you have any update for the problem? I still cannot load all columns from share point list to dataflow created. 

Thank you

Best, Qianru

[v-yuta-msft]

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

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).

[Galia]

@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

[Anonymous]

Is there any update on this issue?

Am still facing this issue with the dataflow when connected to SharePOint Online List.

It doesn't return the attachments relative url and othere default fields which used to come in power bi desktop when connected directly.