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(

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 WHITE If 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