The Baretta

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

If you need to export a list of staff from an Active Directory Group, follow these steps.  Allows you to gather all the names & usernames of staff in a particular group and save the information into Excel. Step By Step How To: Open up Excel > Power Query Tab Click From Other Sources > From Active Directory: (In Excel 2016 this is found under: Data Tab > New Query > From Other Sources > From Active Directory) screenshots from Excel 2016 on left, Excel 2010 w/ Power Query Add-on right A popup will ask you to enter your domain name.  It should already be populated with the correct information but if not, input your companies domain name & Click OK: In the Navigator that opens up, expand your domain tree and double click group: You should now see a list of all groups in Active Directory in the Power Query Editor: In order to find & filter for a specific Group, Click the Down-Arrow for the 'distinguishedName' column > ...

Link to Session:  https://msftignite.com.au/sessions/session-details/1611/real-world-power-bi-techniques-dat343 About the Session: First off, I'd just like to say that Grant stole my idea of starting off the session with a chant of 'Power BI'!  Reckon he must have heard me chanting it on the way into the previous session.  I'm sure he didn't steal it, but was a surreal coincidence haha. This was the final Power BI Session of the week, which is strange because it was the first session to run through step-by-step how to pull back real world data and report on it.  I would have thought the order would have started with the simple sally session then built up to the developer sessions through the week.  No bother for me, but something to consider for the next MS Ignite. Session tips for speeding up your power reports: Clean up your data using Power Query first Create measures to display totals at the bottom of your raw data list. Use brushing / ratios to ...

Today we will be learning how to create a excel report connected to a SharePoint library using Power Query.  I will also address all issues found along the way. The reason we are connecting to our library via Power Query instead of via the 'Export to Excel' option is because our library contains InfoPath forms that have repeating table data in the columns that we need to report on. Power Query   Is a free excel add-in available from Microsoft for both Excel 2010 Professional Plus & Excel 2013.  It is used when you need a way to transform data that isn't quite in the format you need. Step 1: Connecting to your SharePoint Library via Power Query. Open up Excel.  Click Power Query Tab > From Other Sources > From SharePoint List > Insert SharePoint Site URL Here's our first issue.  Only Lists are appearing in the Navigator.  Not to worry, Right-Click any of the Lists showing and click Edit to open up the Query Editor.   You should ...

Power Query uses Microsofts M Language.  I couldn't find out how to comment out code so I could write notes inside the Advanced Editor.  It's the same as C# and many other languages, use two right slashes: // COMMENT HERE!