Posts

Showing posts from August, 2018

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

Image
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]", […

SharePoint Online - Conditional Formatting a Column Between Two Dates using JSON

Image
** UPDATE 16/10/2018: In Q4 2018, microsoft are releasing the capability to do this column formatting using the GUI (no code!):  https://techcommunity.microsoft.com/t5/Microsoft-SharePoint-Blog/Reinventing-SharePoint-business-process-at-Microsoft-Ignite-2018/ba-p/260444

** UPDATE 30/08/2018: I initially wrote this blog at the start of august with the old method (see end of post), however thanks to Dave Paylor (@payl0rd) I found a new, cleaner method to creating conditional formatting which brought my lines of JSON down from 50 to 7.

What
Microsoft have plenty of examples on how to implement conditional formatting on a SharePoint list.  However my specific needs were:

If Date >= [Today] + 30 days Then Background WHITEIf Date <= [Today] -2 years Then Background WHITE (This covers any empty fields)Else Background ORANGE 
Why
I was building a list to monitor licence expiry, applying conditional formatting to these date rules meant that I could highlight a column whenever a licence was a…