Power Query / BI - Data Refresh Error: The column 'Activities' of the table wasn't found

What 
Had an issue in the last few days (approx 30/08/2018) where a Excel Report & a Power BI Report was failing to refresh.  The reports were pulling data from a SharePoint Online List.

Here's the error I was getting

Error: The column 'Activities' of the table wasn't found

Why
When I built these reports, I used Power Query to import the SharePoint List Data & then to make the data easier to look at, I removed all the System Columns that I didn't need to see.  One of these System Columns was called 'Activities'.

Anyway, I'm not sure why, but for some reason when I now get data from SharePoint Lists, the 'Activities' column no longer exists, so the refresh was breaking because it couldn't remove a column I wasn't even using!

How to fix
I just opened up the advanced editor, found the line where I was removing columns, and deleted the reference to "Activities".

Done!

let
    Source = SharePoint.Tables("[SITE URL]", [ApiVersion = 15]),
    #"GetData" = Source{[Id="[LIST ID HERE]"]}[Items],
    #"Removed Columns" = Table.RemoveColumns(#"GetData",{"FileSystemObjectType", "Activities", "Id", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "ContentTypeId"})
in
    #"Removed Columns"

Lesson Learnt
When using Power Query to modify which columns should load to the report, make sure you use 'Remove Other Columns' functionality instead of 'Remove Columns' functionality.  This will mean that even if the Data Source Schema changes, you won't have these kinds of problems.

Comments

  1. Thank you, it helped me to fix my Power BI report as well :-)

    ReplyDelete
  2. This was very helpful. Thank you so much!!!!

    ReplyDelete

Post a Comment

Popular posts from this blog

SharePoint - Field type [FieldName] is not installed properly. Go to the list settings page to delete this field.

Export Group Membership From Active Directory Using Power Query

Office 365 Groups - Quickly find the GUID of a O365 Group