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

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

5 REPLIES 5
qsong
Helper II
Helper II

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
Community Support
Community Support

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

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
Regular Visitor

 

@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

 

 

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.

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors