Friday, 11 December 2015

MS Ignite - Power BI: The Developer Edition (DAT341)

some brief notes on what was learnt in this MS Ignite session

Link to session video:

Covered in the session:
  • A feature to publish Power BI Desktop reports to SQL Reporting Services is coming soon!
  • The Power BI Team is working on tiles able to be embedded on a web page and viewed by anonymous viewers.  Will be able to embed entire dashboard at some point.
  • Link for developer information is at the bottom of power BI home page ( 
  • How to extend Power BI using custom visualisations
  • Can embed Power BI Tiles into PowerPoint presentations
  • REST Api's are available to do things like  list all tiles & dashboard & datasets and tables and groups
  • Can find approved custom visuals here:
  • Can't pin custom visuals to dashboards yet.  team estimates late December
  • You can download Custom Visuals inside the Desktop Application by clicking the '...' shown below

My rushed notes:
  • AAD auth: add application, native client app,  enter redirect URI.  Then configure power BI permission on ur new app in azure.
  • Custom visuals: built in d3.js .  pbi service built with typescript using angularJS.

Develop your own visual:
  • Get github account.
  • Fork the powerbi-visuals repo.
  • Get vs community 2015
  • Install git, node.js.
  • Clone copy of ur forked repo.
  • Run npm install.
  • Getting started.  Before debugging.  Right click pbi-visuals > property pages > set to 'no build'
  • You'll mainly be playing in the clients folder in visual studio.
  • Set index.hmtl as startpage. In pbi visuals playground.
  • Plugins capabilities file will need to be updated if you are creating custom visualisations.
Thanks for reading, if you'd like to know more about Power BI, why not check out some of my previous posts:

Power BI Suites - Which One Do I Use?

I'm presuming you've seen the god-like feature set of Power BI.  I reckon you've probably even opened up browser edition and had a quick play.  Then you've come to the realisation that yeah it is just as awesome as that presenter advised!  But how does it fit together with the rest of the Office 365 suite, and why are there so many different ways to use it!?

Here's a quick historical view of the product and a brief introduction into the pros/cons of each option to help you choose a path based on what your business requirements are.  Truth is, you'll probably want all options available to you, however each new solution might require a certain platform.

If you take ANYTHING away from this post, remember there are three ways you can use the Power BI suite:  Excel Add-Ins, Power BI Online App, Power BI Desktop App...

Excel Power BI Suite (PowerPivot, PowerQuery, PowerMap, PowerView)

Before Power BI became an entity of its own, it was initially birthed as separate add-on's to Excel.  The add-ons are explained in detail below:
  • PowerPivot allowed users to easily pivot millions of rows of data in excel without delays
  • PowerQuery was then introduced to gave users a powerful tool to manipulate & relate data in a repeatable way.  This meant after spending hours cleaning up a dataset, you could now refresh data from a Data Source and it would automatically run the cleanup/washing steps that you had previously performed.  Saving the user having to wash the data every time you needed the latest information.  Power Query also lets your search the internet for public datasets and connect them up with your own private data.
  • PowerMap allowed you display your data on a map.  geographical representing your information
  • PowerView adds additional ways to visualise & interact with your data.  You can even record your interactions for time-based visualisations!

When should I use the Excels Power BI Suite?
  • If you need to display reports in a SharePoint environment, then using excel and displaying on a SharePoint page through an Excel Web Part is the only method at this point.  Currently, Power BI Reports cannot be displayed outside the Power BI platform.
  • If your organisation doesn't have Office 365

Power BI Online Application

This is where Power BI really shines.  If you already have Office365 then you can sign up to the online version of Power BI here:

Easily pull in existing datasets from On Premises or the cloud.  Create reports & dashboards using a plethora of ways to display the data.  Share your results to anyone within your organisation.

When should I use the Power BI Online?
If you already have clean datasets you can easily use and connect to, then use the online edition.  You can't manipulate or wash data so if you need clean your data first then read on.  If you would like to use pre-developed dashboards connected to popular services like Google Analytics, Dynamics CRM, Bing Search, then that can also be done using the Online version.

Power BI Desktop Application

For me, most of my work is done in the Desktop Application.  It is where you go if you need to do some serious development & manipulation of Data & Visualisations.  This is where the Super-User/Data Analyst would spend most of their time.  After you've done the hard work in the Desktop Application, you can still publish the end-result up to the cloud for sharing within the organisation.

When should I use the Power BI Desktop?

  • If you need to be able to wash/manipulate & build relationships on your data before building a nice functional dataset. 
  • If you would like to use some of the custom open-source visualisations available online
  • If you want to use all the features available

Other Consideration (Free or Pro)

Now you know which application to use there's the question of whether you require the features that come with Power BI Pro, or whether you can achieve what you need with the free version.  Below is a feature comparison between the two options (

Thursday, 26 November 2015

HP Laserjet 1022 Printer - Can't Print on Windows 10

If you are having trouble getting your laserjet working on Windows 10, here's how I resolved the problem:

Saturday, 21 November 2015

MS Ignite - Real World Power BI Techniques (DAT343)

Link to Session:

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 gain better insight to your data instead of just slicing/filterin
  • You can easily add multiple columns to your slicer options in the ribbon
  • OLAP tools on pivot tables:  can use this function to move your pivot table data around, means your not restricted to having all the data in the 2-3 columns that the pivot table created.

Power BI will soon support access to SSAS Cubes.

In Power BI Desktop you can:
  • Can import a folder in order to index all the files within it and report on their information
  • Pull table data straight from a Web Page, Don't have to copy/paste to excel first.
  • Can build multiple queries on the one screen, then connect them all up together.
  • Can pivot/unpivot multiple columns, by selecting the ones you don't want to change
  • You can set parameters in your queries so that it asks the user what they want to report on come run-time
  • Can create simple tables and queries in advanced editor too.

Musings:  The session was great for people who already had a background in the Power Suite, but was a pretty fast-paced explanation which I think would have confused some of the less techie attendees.  This meant that the story behind the data was not fully illustrated and the 'real world' connection slightly lost.  I've been to many of Grant's sessions over the years, all have been awesome, so maybe the magic was just lost on me because I'm across all of this technology now!

Still definitely worth a watch for any of you who have not seen Power BI in action!

Session Rating: 3.5/5

MS Ignite - Office 365 Groups Overview, Administration and Roadmap (PRD313)

Session Takeaways:
  • By default any user can create a Office 365 group
  • When a group is created, a container is provisioned to allow staff to collaborate using the office apps of their choice.  This saves waiting for multiple IT teams to provision team sites and distribution groups.
  • Provides business users with an instant way to set up a new project and share information.
  • Enables the end-user a choice of platform without being pigeonholed onto a prescribed application.
  • Groups allow staff who get late access, the ability to see all of the past conversation that has happened.  unlike email distribution groups where the conversation is lost in individual mailboxes.  This will save time having to catch up and save having to ask the same questions if you're new to a project.
  • Can Insta-videocall all members of an O365 Group using Skype for business.  Can also start writing up meeting notes straight away within a call
  • Groups integrate with power BI and Dynamics CRM.
  • Groups REST API's now available.  Allowing you to connect group security with your other Line of Business applications.
  • You can use Azure AD to use group security in hybrid scenarios

For IT:

  • You can set Group naming policies.  Just like how Yammer uses a Bad-Word dictionary.  You can set it up so that staff can't call a group "Shit company" or something just as useful.
  • Can also force groups to have prefixes.  e.g. all Technology groups might need to have 'TECH' added to the name.
  • Showed the 2016 roadmap for O365 Groups

Musings:  I have this concern about how messy groups will get if any punter can create them.  The roadmap shows they are soon adding the functionality to set expiry dates on inactive groups.  That sounds GOOOOOD.

Rating: 4/5

Reasoning:  Kinda similar to first O365 session so they did duplicate some of the information. Getting roadmap information is nice though.  Still got some nice detail regarding groups.  Despite the expiration functionality, I'm unsure how the content that is created within those groups is archived/deleted.

Link to Video/SlideDeck:

Friday, 20 November 2015

MS Ignite - Introduction to Hololens (MOB221)

Many people had mixed feelings about this session, which is ironic given Microsoft are calling the Hololens a Mixed Reality device.

  • No Hololens at the event
  • Every video shown had already been released on the internet
  • We had to get up at 7.30am to not see a Hololens

A few takeaways from the session:
  • You can use Maya to auto-update holograms as your working with it on your computer
  • Microsoft has developed a camera to see what the hololens sees without having to have a hololens on yourself.  Apparently only 2 of these cameras in existence
  • Can collaborate with other people around the world on the same holographic scene
  • There are some battery life concerns.  We were advised to keep this in mind during app development and try and limit the polygon count on any holograms developed
  • Recommended Skillset: Game Studio, Art, Audio, Design, Engineering, Production, QA
  • The development edition of the hololens is being released in Q1 2016.  US only....
  • Field of view: can still see the edges of normal vision

Funny things to mention: The presenter was not pleased about all the Field of View questions that came out at the end of the session.  The response was that no one who has trialed the device has complained about FOV, but instead come out exclaiming that "I walked on fucking Mars!" (In relation to the Mars experience that Microsoft developed with NASA).

A few attendees were quite disheartened by the no-show of the device:

It wasn't disappointing for everyone though:

Session Rating: 3/5

Reasoning: I'd gotten to bed at 3.30am that morning and only got up in the hope I would see a hololens.  I would have preferred an upfront explanation that no device would be shown at the event and that everything shown in the event had already been pre-released on the internet.

Despite this though, it was presented well for people who knew nothing about the device.  I'm just not sure anyone of that calibre existed at MS Ignite.

Wednesday, 18 November 2015

MS Ignite - Office 365: The Next Wave of Innovation (PRD211)

Link to Session:

About The Session:  Exactly what the session description detailed.  Briefly touched on most of the Office 365 / Office 2016 products and what's new.  Here's some dot points if your interested in downloading this session.
  • Delve:  Now has ability to extend it's reach beyond Office 365 products
  • Delve Analytics: Can provide individual users with graphs showing thier time usage, who they connect with most at work as well as who they have fallen out of touch with.  
  • Office 2016: Added search features inside word/excel to find & use features that you can't remember where to find.  Potential to save many calls to service desk and time googling for where to find feature.
  • Excel: Ability to auto-forecast profits & loss based on previous years data inside excel.  Has also been loaded with additional ways to visualize and represent data. (read: more fancy graphs!)
  • PowerPoint: New feature called Morph Transitions.  It utilises existing objects in your slide deck and automatically animates the transition from one slide to another for you.  Helps choose matching colors/fonts/backgrounds to save you the hassle.
  • Outlook: Ability to attach recent files which saves you having to find where the file sits on the filesystem.  Emails auto supply permissions when staff  email/share files.
  • Office365 Groups: Is basically a container of conversations, notes, calendars, files and tasks.  Can be created by all staff removing wait time for IT to action.
  • Threat analytics can now monitor all devices.
Excels Forecasting feature

Follow up session with Marissa is on Thursday @ 9.45am on user-adoption of all these new fancy things.  You can also visit for more guidance on user-adoption.

My Musings: Although they have released a HEAP of new features, Microsoft seem to be putting a lot of effort into improving existing features that people use all the time, rather than just constantly bringing out new things that don't get used.

First concern is, who cleans up all this content (groups, conversations, files, permissions, etc) when its no longer needed?  At the moment there is nothing built to help with this, however I have visited another session where they advised they the ability to set expiry dates on inactive groups.  This feature is on the 6-12 month roadmap.

Last concern is around individual permissions at the document level.  When this is done in SharePoint frequently it can cause loading time of pages & documents to crawl to a halt.  This is due to the permissions table in the database becoming very large & constantly locking for long periods of time while it works out if a user has access or not.  Will this be an issue in O365?  time will tell.

Funny things to mention:  I could see the potential for the delve analytics to be misused by the wrong people.  For example a team leader who wants to micromanage their team and ask questions about how they have spent their time.  Or a HR incident where maybe someone complained that you don't treat them properly and the analytics shows that indeed you take 7 days longer to reply to that person compared with everyone else.  I'm sure this is overreacting, but I'm also sure it will happen somewhere.

Rating:  5/5

Reasoning:  Great run through all the new features.  Strong push for explaining user-adoption is more important than just having the features!  Well spoken speakers.

Tuesday, 17 November 2015

MS Ignite - Certification 101 - Find the Path (EXAM111)

About The Session: Covered the Microsoft certification roadmap.  Discussed Microsoft Academy for kids, educators and trainers.  Spent most of an hour going through how to pass Microsoft exams and what type of questions you can expect.  A discussion around what course material you need to study to pass each exam.

All certification are now solutions focused due to the knowledge required to implement systems successfully.

Went about 25 minutes overtime.

My Musings: The best thing about this session was seeing the official Microsoft certification roadmap, provided below:

The rest of the session was not for me.  I thought that 'Find the Path' was going to be all about explaining in detail what path of certification you might like to take and what you can expect.  However it was mainly about how to pass an exam if you're looking to certify and where you can find resources online to help with your study.

Funny things to mention:
  • MCSE Exams - You have to re-certify every 2-3 years.  Who's gonna do that!  If you're that keen you better sign up to Braindumps.
  • Courses & Exams - 20688 lines up with 70688.  But why the extra numbers in front in the first place!!!  Surely you don't have over 1000 courses!
  • Three exams for most MSCA's - some of the exams xover that cover things from all.  Sounds stupid and confusing.  Just done to make cash.
  • Exams with time limits - sounds like you do not get enough time to read which is silly as it doesn't replicate a realistic setting.  Have heaps of time to find an answer in real life situation and you have Bing/Google at your fingertips.
Overall Rating: 3/5

Reasoning:  The speaker was very clear and concise.  The material was ok for people who haven't done any research of their own on getting certified.  However for me the information was quite high level and commonsensical.  I also lowered the overall rating as I believe the heading of the session was a bit misleading.  However, now I look at the heading again, it does say 101, so maybe I shouldn't have been so suprised!

Tuesday, 10 November 2015

Microsoft Ignite Australia - 17th-20th November

Next week I'm heading to the MS Ignite conference in the Gold Coast and thought I'd sign up to some sessions & get my schedule ready today.

Looking through the sessions, there were two things that stood out to me:

  • A lack of SharePoint sessions
  • A very developer-heavy list of sessions
The first is surprising because SharePoint 2016 preview just came out and I'm not sure anyone is really doing a deep-dive session on it.  Here's what happened when I tried to filter for SharePoint sessions :S:

The second surprise around the mass amounts of developer sessions is because I'm used to Tech Ed events in previous years where they catered for all levels & occupations. Anyone from C-Class (CIO/CEO) down to developer scrubs that want super detail could enjoy the event.  Now I'm not so sure that's the case.

The truth is, it's not that surprising.  For a long time now we've watched Microsoft ramp up it's O365/Azure capabilities to match exactly what On-Premises solutions can offer.

Ignite is going to be ALL about Office 365, Azure & IoT (Internet of Things).  As simple as these platforms are to set up from scratch, they also offer an unlimited amount of power, customisation & integration.  So yes, my dad could probably set up an Office 365 environment and keep everything standard with out of the box features, but at the enterprise level there's going to be a HUGE need for system integration, reporting & customisations.  That's where the developers come in.

There's also quite a few sessions on Skype for Business, SCCM & Device management.

#DistantFuturePrediction:  Working in technology will no longer be about setting up a system and administering it.  That's what Microsoft's cloud offering is for now.  I believe working in technology will primarily be about innovating custom hardware/software to add REAL value to your business & It's customers.

With that said, some large companies are still struggling to work out why they can't pay staff because their Excel Macro is broken.  So the future is still a dream for some.

Thanks for reading, I'll be posting more musings about what I see throughout the Ignite conference so watch this space.

Sunday, 8 November 2015

Windows 10 - Trying to Install Legitimately

Got home from 3 months holiday & my Windows 10 Preview had expired on my only computer at home.

First off, couldn't even boot the computer because certain core windows files had 'expired'.  After changing the date & time in the BIOS to a year in the past, I was at least able to log in.

All good, I knew about the free upgrade from Windows 8.1 which I had a product key for, so I downloaded a copy of Windows 10 pro x64.  

Next problem, USB wouldn't appear.  tried to troubleshoot via Disk Management.  Due to changing date & time in BIOS the computer didn't trust me to open anything that required administration rights....  awesome.

so now cant find any way to get installation media off the computer.  Luckily my partner had a laptop with windows that I could use.  Jumped on it, downloaded windows 10 AGAIN, straight onto a USB stick.

stuck in USB to main computer, tried to install windows 10, formatted main drive, got an error message about installation media not being able to use the partition or some bullshit.  after trying to use DISKPART to zero out the SSD because it's worked for me before, turns out all I needed to do was disconnect my other hard drives while i was installing windows.

Windows 10 installed without me having to insert a product key which is good because I planned on inputting my windows 8.1 product key once it was installed. 

Tried inputting product key, error.  after some research, turns out you can't just put on a fresh copy of windows 10 and use your windows 8.1 product key, you actually have to upgrade from windows 8.  I wanted a fresh start because my SSD doesn't have much space.  So the process you have to follow is, Update to Windows 10 from windows 8, THEN format and install windows 10 and it should automatically recognise your hardware (No more product keys!).

all up, it took about 10 hours to install Windows 10.  legitimately.  maybe that's why they skipped windows 9, the number relates to how long it takes to install... ?

Sunday, 28 June 2015

Connect to Exchange Online via Powershell & Allow File Types

##Use this when you need to make configuration changes to your Exchange environment that can't be done via the GUI

##Connect to Exchange Online via Powershell

Set-ExecutionPolicy RemoteSigned
$UserCredential = Get-Credential
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri -Credential $UserCredential -Authentication Basic -AllowRedirection
Import-PSSession $Session

##How to Allow File Types to be opened in Exchange Online

Get-OwaMailboxPolicy OwaMailboxPolicy-Default | Select -ExpandProperty BlockedFileTypes | Sort
Get-OwaMailboxPolicy OwaMailboxPolicy-Default | Set-OwaMailboxPolicy -BlockedFileTypes @{Remove = ".xml"}
Get-OwaMailboxPolicy OwaMailboxPolicy-Default | Set-OwaMailboxPolicy -AllowedFileTypes @{Add = ".xml"}
Get-OwaMailboxPolicy OwaMailboxPolicy-Default | Set-OwaMailboxPolicy -BlockedMimeTypes @{Remove = "text/xml", "application/xml”}
Get-OwaMailboxPolicy OwaMailboxPolicy-Default | Set-OwaMailboxPolicy –AllowedMimeTypes @{Add = "text/xml", "application/xml”}

##How to Disconnect from Session (important to do when you are finished)

Remove-PSSession $Session

Friday, 26 June 2015

Report on Multiple-Choice Fields in SharePoint Using The Excel add-on Power Query

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 now see a screen showing the data from the list you selected.  We will now modify the query so you can connect to a SharePoint Library.

Once in the Query Editor, Click Home Tab > Advanced Editor.  

You should see a query something like this:

    Source = SharePoint.Tables("http://rootsite/subsite"),
    RandomList = Source{[Name="RandomList"]}[Content]

In order to connect to our Library, we need to change the two highlighted sections.  Replace 'Tables' to 'Contents' and replace 'RandomList' to the name of your library without spaces, like this:

    Source = SharePoint.Contents("http://rootsite/subsite"),
    RandomList = Source{[Name="InsertLibraryNameHere"]}[Content]

If you happen to see the error below, follow these steps to bypass the security measures for connecting to the list data (see Reference link E at the bottom of this post for more information on what this means).

(Formula.Firewall: Query RandomList (RandomList) is accessing data sources that have privacy levels which can not be used together.  Please rebuild this data combination.)

Exit the Query Editor, in Excel: Click Power Query Tab > Workbook Settings > Fast Combine > Select 'Ignore the Privacy levels and potentially improve performance'.

You should now see a list with all the library data!

Step 2: Display Column Data
This is a quick step.   click the Double Arrow next to the 'Attributes' Column, and check which columns you would like to be able to report on.

Step 3: Massage the Data & Create Individual Rows for each cell containing multiple Repeating Table values

This step is the breadwinner.  If you've listed columns that have cells with multiple values in them, then you'll love this (This can occur when you use a multi-choice column in a SharePoint List, or you have data from an InfoPath form with a Repeating Table).

You might notice that rows with multi-value cells only show the first value:

But once you click on the cell, you will see all the values inside that cell.  In my instance, it's listing multiple types of software:

Here's a list of the issues we need to solve here:

  • Problem #1:  we need a common delimiter to split the cell values by.  There's no way to split Carraige Returns (new lines/enter/etc) in the GUI.
  • Problem #2:  We need to turn the column holding multiple values into a List/Table so that we can attack problem #3
  • Problem #3:  In order to report on every individual value (piece of software), we need to insert each value into a new row.

Solving Problem #1:  Here we are going to convert our carriage returns into commas:

  • Right click the Column in question > Click Replace Values.  
  • In Value to Find, insert a the word 'REPLACE'.
  • In Replace With, insert a comma & click OK
  • Nothing should have happened.  Now go back to the Advanced Editor and find the word 'REPLACE'.  Replace that word with the the following: #(lf)
  • Your code should look something like this:

    Source = SharePoint.Contents("http://rootsite/subsite"),
    RandomList = Source{[Name="InsertLibraryNameHere"]}[Content]

    #"Expand Attributes" = Table.ExpandRecordColumn(#"SelectBaseColumns", "Attributes", {"ColumnA", "ColumnB"}, {"ColumnA", "ColumnB"}),

    #"Replaced Value" = Table.ReplaceValue(#"Expand Attributes","#(lf)",",",Replacer.ReplaceText,{"ColumnA"}),
    #"Replaced Value"

Solving Problem #2:  Here we are going to turn ColumnA into a List so we can attack Problem #3.  This also needs to be done via Advanced Editor.  Insert the Highlighted code below into your query:

    Source = SharePoint.Contents("http://rootsite/subsite"),
    RandomList = Source{[Name="InsertLibraryNameHere"]}[Content]

    #"Expand Attributes" = Table.ExpandRecordColumn(#"SelectBaseColumns", "Attributes", {"ColumnA", "ColumnB"}, {"ColumnA", "ColumnB"}),

    #"Replaced Value" = Table.ReplaceValue(#"Expand Attributes","#(lf)",",",Replacer.ReplaceText,{"ColumnA"}),

    ToList = Table.TransformColumns(#"Replaced Value",{{"ColumnA", Splitter.SplitTextByDelimiter(",")}})

Solving Problem #3: Transform Data from your List Column into one row per value.  I did this through the Advanced Editor as well.  Insert the Highlighted code below into your query:

    Source = SharePoint.Contents("http://rootsite/subsite"),
    RandomList = Source{[Name="InsertLibraryNameHere"]}[Content]

    #"Expand Attributes" = Table.ExpandRecordColumn(#"SelectBaseColumns", "Attributes", {"ColumnA", "ColumnB"},       {"ColumnA", "ColumnB"}),

    #"Replaced Value" = Table.ReplaceValue(#"Expand Attributes","#(lf)",",",Replacer.ReplaceText,{"ColumnA"}),

    ToList = Table.TransformColumns(#"Replaced Value",{{"ColumnA", Splitter.SplitTextByDelimiter(",")}}),

    #"AddTempColumn" = Table.AddColumn(ToList, "TempTableColumn", each Table.FromColumns({[ColumnA]})),

    #"AddAllRows" = Table.ExpandTableColumn(#"AddTempColumn", "TempTableColumn", {"Column1"}, {"ColumnB"})

Step 4: Clean up Columns and Load Data to Excel
Now that we've successfully massaged the data and got it presented how we want, now it's time to load it to excel.

Before loading it to excel, Remove all Binary, List & Table Columns from the data.  This is because once it's pushed to Excel, excel can sometimes refuse to load when these columns are still displayed.

Once the columns are removed, Click Home Tab > Close & Load to push to excel.

We are done here.

Step X:  Additional Useful Information.
My list actually had MULTIPLE columns with multi-value data.  So here's the final code I used to create all the columns as List Columns, then combine them into the Table Column together, then Expand them all together (Because the repeating table data was all related to each other).  You may notice I skipped the 'Change to Comma' step.:

    Source = SharePoint.Contents("http://rootsite/subsite"),
    RandomList = Source{[Name="InsertLibraryNameHere"]}[Content]

    #"Expand Attributes" = Table.ExpandRecordColumn(#"SelectBaseColumns", "Attributes", {"ColumnA", "ColumnB"},       {"ColumnA", "ColumnB"}),

    ToList = Table.TransformColumns(#"Formatting-Version",{
        {"ColumnA", Splitter.SplitTextByDelimiter("#(lf)")},
        {"ColumnB", Splitter.SplitTextByDelimiter("#(lf)")},
        {"ColumnC", Splitter.SplitTextByDelimiter("#(lf)")},
        {"ColumnD", Splitter.SplitTextByDelimiter("#(lf)")},
        {"ColumnE", Splitter.SplitTextByDelimiter("#(lf)")}

    #"AddTempColumn" = Table.AddColumn(ToList, "Temp_ListColumn", each Table.FromColumns({[ColumnA],[ColumnB],[ColumnC],[ColumnD],[ColumnE]})),
    #"AddAllRows" = Table.ExpandTableColumn(#"AddTempColumn", "Temp_ListColumn", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"ColumnAA", "ColumnBB", "ColumnCC", "ColumnDD", "ColumnEE"}),

    #"Remove Columns" = Table.RemoveColumns(#"AddAllRows",{"ColumnA", "ColumnB", "ColumnC", "ColumnD", "ColumnE"})


   #"Remove Columns"

REFERENCES (and thanks!):

Thursday, 25 June 2015

How to comment out code in Power Query Advanced Editor

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:


Tuesday, 9 June 2015

SharePoint 2010 - Global Reusable Workflow missing from Workflow List

First off, if you can get the cash, pay for Nintex Workflows.  It is worth it's weight in gold.  I cannot stress enough how easy it is to build, copy, manage, update workflows with Nintex.

Simply, you're wasting your time/life if you are still building workflows in SharePoint Designer.

However, if you're just working out how to keep using an old reusable workflow and it's not showing up as an option to use in your list, here's how I got it to show back up.

Add another Content Type to the list (even if just temporarily), this seems to force the list to check which Workflows are available for the Content Types in the List, and the workflow should show up again.

SharePoint 2010 - Powershell Script to Disable Versioning on a specific List & Delete Old Versions

This will remove versioning from the list you specify & update every item so that the old versions are deleted.

Great for really large lists/libraries that don't require versioning when you want to free up some space.

$web = get-spweb "http://root/site"
$list = $web.Lists["List Name"]
$list.EnableVersioning = $false

foreach($item in $list.Items) {

Tuesday, 19 May 2015

Issue with Embedding Yammer Feed in SharePoint 2010

On the 14th of May 2015, Yammer deployed an update to their Embed Yammer Feed script that stopped any Yammer Feeds on SharePoint 2010 from working.  In our case, the Yammer Feed got stuck on 'Loading...'

This error that is causing the Yammer Feed to fail to load is:
Object doesn't support property or method 'forEach' . yam-platform-feed-57f4f449766511e4f91eb519a39b2781.js, line 480 character 19099

The reason this is happening is because SharePoint Master pages have a line of code which forces all Internet Explorer Browsers to run in IE8 Compatibility Mode.  This is done to ensure all the ribbon & SharePoint functionality continues to work on new browsers.

The line of code in question:  <meta http-equiv="X-UA-Compatible" content="IE=8"/>

Hint: don't just remove this line of code!  you'll cause yourself a headache.

The line that Yammer & Microsoft are taking is that if you plan to stick to SharePoint 2010, the only way to use Yammer is to download the Yammer Web Part for SharePoint 2010 which is only being supported until March 15, 2016.  After that, you'd wanna not be on SP2010 anymore if you want Yammer functionality on your intranet.

Workaround to keep using Yammer Embed:

However, here's a workaround that may cater for your needs:  Wrap the compatibility code around around a SPSecurityTrimmedControl so that doesn't run in Compatibility mode when staff only have Read-Only Access.

<Sharepoint:SPSecurityTrimmedControl runat="server" Permissions="AddAndCustomizePages"><meta http-equiv="X-UA-Compatible" content="IE=8"/></SharePoint:SPSecurityTrimmedControl>

There's a few caveats with doing this though:  your site-admins cant see the feed, there may be other pieces of functionality that need IE in compatibility mode even for reading sites/pages.


Sunday, 19 April 2015

SharePoint Farm Freezing, Becomes Unusable Without An IISReset - A Lesson In Item-Level Permissions

Recently I was working with a client who's SharePoint 2010 environment had been poorly managed for years.  They had a single site collection on a single, large content database (650GB).  Not uncommon, however this environment also occasionally froze up, never loading any of the pages on any sites until an IISRESET was performed on the Web Front End (WFE).

It started off only happening once a month, then moved to once a week, a few times a week, to 5-10 times a day...

Begin Troubleshooting:

The issue was around too many lists, and items within those lists having their own permissions.

Each permission for an item counts as one row in the RoleAssignment table in the database.

apparently they say a healthy DB will only have 200,000 rows in that table.

Ours had 10,000,000 rows (You can check this by right-clicking the Database in SQL Management Studio and clicking Reports > Disk Usage by Top Tables.  Then look for the RoleAssignment Table).  and every time someone went to check permissions on a big list, bang.  it locks the table while it finds the data (which takes ages to traverse), meanwhile every other computer trying to do anything on the site dies while the table is locked.

The ONLY way to fix this is to remove any tables with HEAPS of item level permissions.

I got two handy scripts for this.  both below:

Script #1: a SQL script to find the lists that had the most item-level permissions

select COUNT(ra.PrincipalId) as [Count],p.ScopeUrl from RoleAssignment ra with(nolock) 
join Perms p with(nolock) 
on p.SiteId = ra.SiteId and p.ScopeId = ra.ScopeId 
group by p.ScopeUrl 
order by p.ScopeUrl desc

Script #2: a SharePoint Powershell script that checks if a list has item-level permissions, then gives you the option to set every item in the list to inherit the Lists permissions:
  • Restore-Inheritance.ps1 "http://SPURL/SITE" "LIST NAME" true

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) { 
    Add-PSSnapin "Microsoft.SharePoint.PowerShell" 


$webUrl = $args[0]
$listName = $args[1]
$listInherits = $args[2]

# Varibale to hold document count
$count = 0


try {
# Open the web and list objects
$web = Get-SPWeb $webUrl
$list = $web.Lists[$listName]

# If the list should inherit, reset the role inheritance
if ($listInherits -eq $true) {
Write-Host "Updated permissions on list." -foregroundcolor Green

# Get all items with unique permissions
$itemsWithUniquePermissions = $list.GetItemsWithUniquePermissions()
Write-Host $itemsWithUniquePermissions.Count "number of items with unique permissions found."

# Only update items if some exist
if ($itemsWithUniquePermissions.Count -gt 0) {
foreach ($itemInfo in $itemsWithUniquePermissions) {
$item = $list.GetItemById($itemInfo.Id)

# Display number of items updated
Write-Host "Updated permissions on $count items." -foregroundcolor Green
else {
Write-Host "No items with unique permissions exist, nothing to update."

# Dispose of web object
catch [Exception] {
Write-Host "Exception encountered.  Please ensure all arguments are valid." -foregroundcolor Red
Write-Host $_.Exception.Message -foregroundcolor Red


Thursday, 9 April 2015

Online Shopping is King

This is how bad it is to actually have to walk into shops and try and order something nowadays.

The bonnie lass & I recently scored a $100 Adairs* gift card and thought it was about time to go purchase something fancy with the "cash".  Normally we'd just look online, which we did, but we couldn't use the gift card online.  So we headed off to the Analog store.  After perusing the store for quite some time and being unimpressed with the range & the prices, we decided upon a quilt cover that we thought suited our needs.

After the store clerk tried calling multiple Adairs' stores in the region, it turned out they didn't have the quilt cover in our size (the ever illusive Queen size quilt).  No bother, we went back to the wall and chose another quilt cover that we were reasonably happy with.

Oh what's that you say?  You checked out the back, tried calling a few stores and you don't have a Queen in this print either? hmmm.  OK.  Well fuck it, we're here now, we've wasted enough time in this store, how about this one (choice number three).

"Oh yes, we have a Queen!" the store clerk declares, quite disbelieving of the fact herself.  "Would you like the matching pillow cases?" she asks next.  Oh dear, it turns out for some (villainous) reason this specific quilt does not come with pillow cases by default because the smug home designers out there thought it would be clever to allow people to mix and match, therefore allowing maximum creative design to enter the humble abode.

I'm not even sure where to begin with the logic here.  So I guess I'll just jot down some dot points as to why I think this is absurd.

  • Who wouldn't want matching pillow cases?
  • The quilt cover on it's own cost $129 (on special,  normally $170-something).
  • Who thinks they are clever enough to match a quilt better themselves by not choosing the same pattern?
Back to the story.  By this time we were exhausted trying to find something in their store we could buy, so we took the pillow cases when the store clerk asks "do you need chscshchschsch** pillows as well?".  No.

I noticed as she was walking to the counter with all the 3rd rate gear that the pillow cases were $58.95 each.  I knew the store had a special so made note to check the price as she scans them into the computer (let's not even dig into the fact that two pillow cases are the cost of a quilt cover).

Scan scan scan, total price $209.90.  We stare at each other, shrug at the hopelessness of it all and hand over gift card plus another $109.90 because it was time to get the hell out of there.  Now I can't belittle the clerk too much, she did recognise our hesitation and advised we could exchange if within 30 days.

Get home, put covers on, looks shit.  frustration came first, then sadness, then determination.

Packed all the gear back up, returned to the shop the next day.  asked to exchange, all good.  Found another quilt cover we decided would look better in the room (yeah we will never become interior decorators).  $109 WITH pillow cases.  ace.

still have $100.90 cents to spend.  sweet.  lets get those two cushions and that towel, that brings us to $209.95 cents.  perfect.

clerk 2 (who was probably frustrated that we returned something (how dare we)) then advises we still owe 0.05 cents.  when I heard that, what I really heard was "Please never return to our store again because it's just not worth your, or my, time".  Yes it's true we owed 5c, but is it really going to affect your bottom line to let us get away with it?

If there's any moral to this story it's that online shopping is way better than leaving the house.  Online stores should also start allowing customers to use their Gift Cards online.  Lastly, Never shop at Adairs.


*Adairs is a homeware & manchester store for those not in the know.  Though hopefully not for much longer.

**The sound of her pronouncing the fancy french name for big pillows, also the sound of a tv tuned to no channel in particular, because I didn't care.

Thursday, 22 January 2015

Can't Connect to SharePoint Designer OR can't query GetUserProfileByName Web Service

We were getting an issue on one of our Test Server WFE's where we were having two issues:

- If you were trying to load an InfoPath form that contained a connection to the GetUserProfileByName Web Service, it would fail.
- If you tried to connect to the site via SharePoint Designer, it would error out.

SO!  If you get any of the following issues:

In SP Designer:
"The server could not complete your request.  For more specific information, click the Details button."

"An error occurred while trying to fetch data from your SharePoint site.  Unexpected response from the server.  The content type of the response is "", The status code is "OK"."

On an InfoPath Form:
"There has been an error while processing the form.  An error occurred while trying to connect to a Web service"

In SharePoint Logs:
"The following query failed: GetUserProfileByName (User: xx\username, Form Name: Template, IP: , Connection Target: , Request: http://root/site/Lists/ListName/AllItems.aspx, Form ID: urn:schemas-microsoft-com:office:infopath:list:-AutoGen-2014-11-13T05:55:39:175Z Type: DataAdapterException, Exception Message: The remote server returned an error: (401) Unauthorized. The remote server returned an error: (401) Unauthorized.)"

Here's how we solved the problem:
Navigate to the server in question
Open IIS Manager > Navigate to the site that is causing the problems
Click on Authentication > Windows Authentication > Providers...

Our Enabled Providers had been changed, and was set to Negotiate: Kerberos (wasn't the same on the prod servers...).  So I changed the Enabled Providers back to 'Negotiate' & 'NTLM'.

After an IISRESET, good as gold.

Thursday, 15 January 2015

InfoPath, Content Types, Site Columns & The Future

Just a quick one after some deep thought.

Thoughts on the Future: If you have to create an InfoPath Form, you should be creating it as a List Form (if possible), not a Form Library.  The reason for this is ease of upgrade now that InfoPath will not be upgraded/supported by Microsoft.

If you have to create a Form Library (because you form requires repeating tables/sections):

I’ve decided that InfoPath forms should NOT be published as Content Types.  The only time it would be considered OK is if the form is very generic and is being used on multiple sites.  E.g. a Help Form which could be used by multiple departments without any modification.

The main issue when publishing as a Content Type is that every field you promote to be visible in SharePoint has to be created as a Site Column. This means that even for a small form, for instance, a Personnel Access Form, you’d have to create Site Columns for fields like: Room Number or Access Hours, which would only ever be used for this form and nothing else.

Site Columns (and Content Types!) should only be created when you are planning to re-use them across multiple sites. They shouldn’t really be created to be used on one specific thing.

With that said, It’s still worth creating a few Site Columns for fields you know will be in the majority of your forms, and connecting each forms promoted fields to them. Connecting these fields to Site Columns is useful if you ever want to do some Search Customisation & Filtering on the columns.
TL;DR: Creating full InfoPath forms is a bad Idea.  But if you have to go for it.  you may just have to rebuild the form in 10 years.  If you ARE publishing a full InfoPath form, don't publish as a Content Type.  Instead for search purposes you should create 10 or so commonly used Site Columns and publish forms as Form Libraries, linking up the fields from your form with the Site Columns.

Monday, 12 January 2015

AvePoint DocAve 6 Manager - Admin Account Disabled

Today I tried to log into our DocAve 6 Suite on our SharePoint 2010 environment and came across this error message:
Sorry, the account has been disabled. Please contact the administrator
My guess was that due to inactivity (hadn't logged in for a few months) the accounts had been disabled.  The problem was that every Administrator account had been disabled so I couldn't get in!

After a quick call to AvePoint, the techies solved the issue quick smart.  Here's how you can save yourself a call and Enable a user accounts status again:
  • Log onto the SQL Server that stores the AvePoint Databases.
  • Navigate to AvePoint's ControlDB
  • Right-Click > Select TOP 1000 rows for the table dbo.AccountMapping
  • In the results, you should see all your accounts, and a Status column which states whether the account is disabled or not (1 = disabled, 0 = enabled)
  • Right click the dbo.AccountMapping table and Edit TOP 200 Rows, and modify the Status from 1 to 0 for the account in question.
  • You should now be able to log in with that account.
To stop this issue happening again, log into DocAve and navigate to this area to change the length of time accounts can remain Inactive for: Control Panel > Security Settings > Inactive Period