Archives: June 20, 2022

Have you ever wonder how to conditional format a Line Chart in Power BI which displays colour coded markers?

I came across this amazing tip from Curbal which explains how to do this. This blog is how I used the example by Curbal for my own use.

I was recently tasked with creating a Dashboard Visual in Power BI which showed a monthly percentage trend of timesheets that had been submitted on time.
The aim was to have a visual which :

• Showed the monthly trend (a line chart can do this easily)
• Visually showed if the value on each month had met its target (not possible do with just a line chart)

The Line Chart was the perfect visual to use; however, there was no simple way to apply a KPI colour on each month. The markers section on the Line Chart did not have the option to apply colour coding, see image below

Solution

As with anything i do in Power BI, the first thing i did was present my data in a table format.

Below is the raw (Sample) data i need plotting on my Line Chart, with each month having a marker that was colour coded.

The Submitted Time % was calculated via a Measure Called [TimeSheetCompletion] (For the purposes of this blog, i will not go into how i got the values and assume you have you base values.)

Step 1

Create categories to bucket your values allows you to sort the Submitted Time in a relevant buckets which can be used when applying conditional formatting.

Below is the Switch Statement i wrote to achieve this :

KPI Code =
SWITCH (
    TRUE (),
    [TimeSheetCompletion] >= 0.95, 1,
    [TimeSheetCompletion] <= 0.94
        && [TimeSheetCompletion] >= 0.90, 2,
    [TimeSheetCompletion] <= 0.89
        && [TimeSheetCompletion] > 0, 3
)

This code is a simple Switch Statement which categorises the Submitted Time into buckets of 1, 2 or 3.

• If the Submitted Time is Greater than or equal to 95%, then the output would be 1
• If the Submitted Time was Less than or equal to 94% & greater than or equal to 90%, then the output would be 2
• If the Submitted Time was less than 80% and greater than 0%, then the output would be 3.

Once we apply that KPI Code measure to the table, we get the following table

Step 2

Remove the KPI Code and Convert the Table above to a Stacked bar chart, as below

Step 3

Format Visual, The Bars Section has Colours that allows you to apply conditional formatting.

Step 4

Apply Conditional Formatting
Select the fx button, under Format Style, choose Gradient. Select the KPI – Code Measure created above as the field to base this on.
Add a Middle Colour and set the Custom Values to 1 2 and 3 and choose a colour for each Value, as below

Step 5

Your stacked bar chart should now look like this

Step 6

Now convert the Stacked Bar Chart to a Line Graph.
Voila; we have a colour coded Line Chart.

Hope that helps someone along the way 🙂
If there is a better or more efficient way of achieving the above, please let me know in the comments.

Thanks

Last week, I thought I would take the plunge and purchased the Google Nest Hub 2nd Generation. The Primary reasons for purchasing the hub was for music and reminders.

Below is my review on the Hub with a link on how to purchase.

Overall Verdict

You can purchase the Google Nest Hub for a budget price of £45 via the following link : https://ebay.us/6V3AHr

Features

Google Assistant

This comes with added visuals, for example, you can ask Google what the weather is like and you will get a weather graphic for the day. Ask google for a route to a certain destination, it will bring up Google Maps. Ask google to play a playlist of Spotify, it will find it and play it whilst also showing your album with on screen controls.

Voice Recognition

Ask Google how your day is working, it will tell you about your diary.

Mute

There is a mute button on the back of the device, so when you dont want the device listening to you, you can ensure it wont be listening.

Home Automation

Although this feature is not something i have utilised yet, you can connect the hub to Hive Active Hearing system. You can also set up the Nest up with your Google Home app

Digital Photo Frame

You can display all google photos on your hub on a loop, and whats more you can even ask the devide to play your photos from a location or a specific date.

Sensors

The Ambient EQ sensor, which detects the light in the room and adjusts the screens brightness to suit you.

Sleep Sensor

I don’t particular use this as it will be a paid feature in 2023, but it may be something i use in the future. Google added a sleep tracking feature called ‘Sleep sensing’. This feature detects motion, so placing the hub on a bedside table and it will start gathering sleep data where is checks for movement, breathing at night which ultimately helps you gain a better understanding of your sleep patterns. Over the course of time, the hub will begin to offer recommendations on things you can do to improve your sleep.
The size of the detailed below

Quick Gestures

The quick gestures lets your play or pause a song or video or snooze the alarm by waving your hand. It works by the built in motion sense technology that detects your hand moving without the use of a camera. I would defo recommend using the demo on the app on how to get this to work, as it can be a bit tedious)

Broadcast to Nest Speakers

This is a cool feature where it allows you to attach Nest Speakers around the house to play music all around the house. You can adjust the volume on individual speakers in each room.

Design

The 2nd Gen Hub looks just like a tablet with a edgeless screen perched on a fabric covered oval stand with a speaker in it, giving the floating display vibe. The 7inch size is a perfect size to watch video clips, You Tube or even Netflix..
The Display has a Ambient EQ, which uses a sensor to adjust the ambient light in the room. It detects and matches over 16 million combinations of light and colour to blend in.

The design spec is as below :

Size : 7 x 4.5 x 2.7 inch
Display : 7-inch 1024 x 600 pixels
Speaker : 1.7 inch tweeter
Camera : None
Wireless : 802.11a/b/g/n/ac WIFI, Zigabee,Bluetooth 5.0

Audio Quality

The Hub uses a 1.7 inch tweeter and has three built in far field microphones.
If you are looking for the best sound, I advise purchasing additional Nest Speakers which you can attach to the hub

Privacy

The device is designed without a camera, (and for good reasons, as it can be placed in a bedroom). This means there is no room for any breaches in privacy.
The microphone can also be turned off from the back, when off, the small orange indicator light appears.

Conclusion

I personally think, for the price you are paying, Google have offered a fantastic product, features such as streaming support, smart skills, sleep monitoring, acceptable sound, couple with the ability to add Google Nest speakers to the hub offers a fantastic all round product. The fact there is no camera and the ability to switch of the microphone gives me confidence my privacy is maintained.

The Hub needs to be plugged in at all times, it would have been nice to have battery operated device which you could charge as the hub needs to be close to a socket in order to work.

Google Duo is required to take advantage of the voice calling, which is OK if both people use Duo, for me personally, I don’t know anyone that uses Duo.

I would recommend changing a few settings in the Google Home app to ensure activity is not monitored or saved, but apart from that a perfect device for the price I paid : )

Introduction

I recently developed an Inventory app for a client. The whole app (built in Power Apps), is quite a big app, so I have decided to break this blog up into 3 parts.

1. Using location services (This blog)
2. Using the Barcode Scanner to scan items
3. Storing Scanned Items

Scenario

The scenario for this app was that is was going to be used by reps to do an Inventory at nearby factories, i.e. There are several factories in a certain geographical location and reps (using their phones GPS) can see which location is nearest to them.

Using Location Services in Power Apps

1. Drag the Image Control onto your Power App and assign the following code to the Image Property, as below. (You will have to get your own API Key. I used the following link :
   https://developers.google.com/maps/documentation/places/web-service/get-api-key

"http://maps.googleapis.com/maps/api/staticmap?center=" & LocationTable.Selected.Customer_Latitude& "," & LocationTable.Selected.Customer_Longitude & "&zoom=17&size=700x900&key=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx&markers=color:red%7Clabel:A%7C"& LocationTable.Selected.Customer_Latitude & "," & LocationTable.Selected.Customer_Longitude

As you can see, you have to specify a Latitude and Longitude (and the api key) in order to get the Map to work.

The reason I have developed it like this is because the app should allow a rep to select which location they wish to go to. So there will be a list of locations, and the rep and choose which one he or she wants.

Adding Locations

1. I create a new page on my app called ‘Config’ which is not visible to the end user. The location should be filled in prior to the reps accessing the app.
   Its a very basic page which can you customize to your needs as you wish. For the purposes of this blog, i am using Microsoft Office as factories.

This page simply allows you to Add an Location and then store that location inside of a Collection. The Longitude and Latitude is from Google Maps.

To add the address to the Collection, the Add Location has the following code on the OnSelect Property,

Collect(Locations, 
{
    Customer_Title: txtCustomerName.Text,
    Customer_Address: txtAddress.Text,
    Customer_Latitude: txtLatitude_1.Text,
    Customer_Longitude: txtLongitude_1.Text
}
)

The value entered is then saved to the Collection, e.g. below

You add as many locations as you want to the Collection and then display the collection directly below the map, as below (You will have to go into Data Sources, Add Data and choose the Data Collection you want to add) – You will have to ensure that your Data Table is called LocationTable as the api key above references it.

The aim here is to add several Locations, then the rep can select each location and the map should change accordingly,

Conclusion

This blog was part 1 / 3 where we added location services to the app, so a rep can select a nearby factory. Watch out Part 2 of the app will be using the phone camera to scan barcodes.

Problem

Asset information containing information about office equipment borrowed by employees was stored inside of an SharePoint List. (For the purposes of this blog, I will use fictional sample data).

We needed to automate a way to send emails to remind employees of the items they borrowed and when they were due back.

The problem was that one person borrowed several items and we did not want to send that one person separate emails for every item they borrowed. We needed a solution that send a summary email of all the items borrowed. So if Joe Bloggs borrowed 10 items, he should receive one email listing the 10 items.

I developed a solution using Power Automate, that I hope will help someone.

Sample Data

Firstly, I will be using sample fictional data to demo how I built this solution, see sample below

You can see from above jbloggs@domain.com has borrowed 4 items, but has returned one item, so he should receive an email listing only the 3 items he has borrowed – (Not the returned item)
jsloan@domain.com has borrowed 3 items, but has also returned 2, so she should only receive an email listing the one outstanding borrowed item – and so fourth.

Solution

For the purposes of this demo, I am using the Manual Trigger Action to initiate the flow; however, you can use this email to send on a recurring basis if you need to.

Using Power Automate, I connected to the SharePoint List using the Get Items Action, and then using the ODATA Filter, I filtered out assets that had already been returned, as below

This only returned items from that SharePoint list where the Returned Value was set to No. (Note : I use JSON Viewer to view output of results, as below)

Directly below, I added a Select Statement to Get all the Asset borrowers from that SharePoint List and map it to a variable called ‘Email’, as below

The returned the following output :

[
  {
    "Email": "john.doe@domain.com"
  },
  {
    "Email": "jsmith@domain.com"
  },
  {
    "Email": "jbloggs@domain.com"
  },
  {
    "Email": "jsloan@domain.com"
  },
  {
    "Email": "jbloggs@domain.com"
  },
  {
    "Email": "jbloggs@domain.com"
  }
]

(Remember, we are returning all the Emails who have borrowed items Only, and assigning the variable Email to their Email Address.)

Next step, I added a Compose Action and added the following Expression to get a unique list of Emails (or a distinct list of emails)

This returned the following JSON,

[
  {
    "Email": "john.doe@domain.com"
  },
  {
    "Email": "jsmith@domain.com"
  },
  {
    "Email": "jbloggs@domain.com"
  },
  {
    "Email": "jsloan@domain.com"
  }
]

You can see now we have a unique or distinct list of email address.

I then added an Apply to Each (Which i have renamed to ‘ForEachEmail), and added in a Filter Array inside of the Apply to Each, as below

This Apply to Each loops through each of the Unique Emails and lists all the items for that Email

Now that we have have all of our Unique Emails and all the items they have borrowed, the hardest part is done.

Next is the Clean up and to make it look presentable.

Cleaning up the Output

I added a Create HTML table just below the Filter Array, and instead of selecting Automatic Columns, I selected Custom and defined my own columns, as below.
The reason for this is because if you select Automatic, the HTML will show every single column inside of SharePoint, such as the Created Date, Modified Date etc

This produces the following Output for each Unique Email,

Now, for each Email, we have an output presented as a HTML Table 🙂

I didn’t quite like the output design of this HTML, so I did a quick search and came across this blog which gave fantastic examples of HTML Styling.
I created a Compose Action, which I renamed Style and copied in the HTML Style found on Ryan’s Blog, as below

The Next step is to put all of this into an Email.

Composing the Email

I added the Send an Email (V2). Ensure the email is sent to the right person, by inserting the Expression as body(‘Filter_array’]?[0]?[‘EmailAddress’]

An important step is to add the Styling to the body of the email. There are two ways to do this :

1. You can edit the email in Code View and insert the styling.
2. You can add the Styling Action directly into the email, as I have done below

Once the Styling as been added, you can compose the rest of the email as I have highlighted below

Now to give it a try, see below for the results of the email received and how the Email Looks

I hope this has helped someone, please feel free to comment below for any questions.

Please read my disclaimer before making any purchases.

Earlier this year, I started a project of renovating my kitchen.
Before even looking at new kitchen designs, my first action was to get the exact measurements of my existing kitchen.

Traditionally, I always used the ‘normal’ tape measure where you measure the distance between A and B, however, the biggest disadvantage using this was its difficult to get an exact reading.

DTAPE Review

I came across a Digital measuring tape, and I purchased the below tape from Amazon. Its not the best measuring tape, but for what I required it for – it was perfect.

The main advantage of this laser tape is the speed at which is works. You simply face the laser towards a hard surface of another area of where you want to measure, (e.g. wall) and it correctly gauges the distance via a laser.

Whilst the measuring tape is at the lower end of the spectrum compared to the some of the named tapes, I could not fault it for what I required it for.
There probably wont be a time I need to measure more than 100m.

The backlight was bright enough for me (even outside), the accuracy was spot on (I have only measured no more than 10metres) and a fantastic feature is that the screen records the past 20 measurements which allows you to take several measurements in one go and cut all my pieces at once, opposed to having to measure each piece of wood individually and then cutting.
The tape also measures the area and volume and the measurements can be adjusted from metres, feet and inches

My overall verdict is that you don’t have to spend hundreds of pounds on a measuring tape, when this one looks fine is affordable and does an excellent job,

You can purchase this tape using this link : https://amzn.to/3KEtWvZ

Problem

I had a scenario last week where I created a MS Form inside of my MS Team and wanted to capture the responses and do some validation on them.

Flow can easily connect to a MS Form and carry out some actions once the form is submitted, right?

The problem was that when I created my Forms inside Teams, I was unable to select the form from within the Flow.

Solution

• First step, within my Microsoft Team, I created a Form as below

• I navigated to Power Automate and created a new flow for when a form is submitted, as below

However, when you expand the ‘When a new response is submitted’, you do not see the option to select the Form you just created inside of your Team.  I can see all the other forms I created from directly within MS forms, but not the form created from within MS Teams.

I was baffled by this for quite a while. Until I thought of trying something!.

• I navigated to the forms (https://www.office.com/launch/forms?auth=2) I actually saw my form Teams form there, so I opened it up and I copied the FormID from the URL

• I pasted the Form ID manually into the ‘When a new response is submitted’ section, as below

• Added the Get Response Details action and again, manually entered the Form ID as below

And the Flow Worked 😊  – Hope this helps someone and avoids them wasting time like I did .

This blog post was originally written in December 2014 – The post was copied from my old blog (Which is no longer active) site to here

Before reading any further, I strongly recommend you test, test and test this before implementing in a Production Environment as per my Disclaimer

Back in 2014, I was tasked with installing an Intranet / Extranet for a customer.  Installing and configuring the SharePoint 2013 (on-premise) was all done, the customer however required secure communication over the extranet so external users could communicate securely over http.
This blog article will detail how I set up SSL (Secure Socket Layer) and shall furthermore describe how SSL works.

Extend the Web Application Zone

Firstly, I extended the Intranet URL to the Extranet Zone.  This ensured that the external users could access the same information as Internal Users.
(When you extend a zone, SharePoint automatically creates an Alternate Access Mapping (AAM), this basically tells SharePoint how to map the request to a URL)

1. Highlight the web application you wish to extend and select the extend tab from the ribbon

2. From the Next screen I then populated the following fields

Ensure the Extended zones is on Port Is 443

Ensure the Extended Zones is configured to run on SSL

Check the URL and the zone is correct

Once you Click OK – SharePoint will create an additional zone for you in IIS (as below)

Create your IIS Certificate

3. To create the Certificate, in IIS click ‘Create Certificate Request’

Populate the Distinguished Name Properties (as below) and Click Next

Save the .txt file. The txt should be used to request the certificate from a verified issuer such as VeriSign or GoDaddy.

Install Certificate

3. Once your certificate .cer file is on the web server, right click it and select Install Certificate. (If you have multiple WFE servers, do this step on all of them)

4. Ensure the certificate is imported onto the Local Machine and placed in the Personal Store.

5. Head back into IIS and double click Server Certificates.

Your Certificate will be visible.

Edit SharePoint Bindings

6. Go back to the extended SharePoint Site, Right Click and select Edit Bindings

select Edit

Select your certificate from the SSL dropdown and Select OK.

7. Your Certificate is now attached to your Web Application.

Move the certificate into the Trusted Authority Store

8. Navigate to your Extended SharePoint site and assuming you have your DNS records set up; it should load with problems with the green padlock (as below)

How to SSL Work?

Now having set all that up; what does attaching a certificate to the SharePoint server actually do?

1. Your computer makes a request to access the http://extranet.domain.com site

2. The server where the certificate is installed for http://extranet.domain.com issues a Public Key to the requesting computer.

3.Your Computer then encrypts the data with the Public Key (that was sent from the server) and sends data back to the server.

4. The Server then decrypts the data using the Private Key – and only the server with the Private Key can decrypt the data.

If anyone intercepts the data in between, the data will be ‘Jibberish’.  The Private Key is the only way the data can be decrypted.

This blog post was originally written in May 2016 – The post was copied from my old blog (Which is no longer active) site to here

Have you ever tried to use a SharePoint list to populate a Power Pivot Graph in excel; and then render the graph in SharePoint using Excel Web Apps? – Sounds pretty cool eh!?

Not Quite.

I created a SharePoint list, for e.g., Profit Loss, which details a financial summary of a project, e.g.  (Profit is a calculated column)

Having created the list, I opened up Excel and created a OData Data Feed connection (below) and generated a graph based on the data within the list.

Graph below

Seems relative.

However, if you make a change to the SharePoint list, the excel web access web part in SharePoint does not dynamically update to show an updated chart to reflect the change made on the list.  You must manually open the excel sheet, refresh data connections and then save the excel sheet again to update the excel web access web part.

If you manually refresh the Excel web part by clicking Data, Refresh Selected Data Connection, the web part loads the new updated data;

Screenshot below showing new data

Screenshot below showing a manual refresh

Result after doing a Manual Refresh (notice the profit)

If the entire page is refreshed, the webpart reverts back to the original, see image below.

I noticed that if you have the excel sheet open in real time and make a change to the SharePoint list – the Excel will in fact update – but it does not save the sheet. (You can check the modified date on the library).

Having done further tests – I also noticed the Pivot Chart had a ‘Refresh data when opening the file’ – I checked this and saved the excel file to my SharePoint document library. 

The Excel Web Access web part now showed a Warning when the page was loaded, as below

The user must click Yes to load the worksheet – which as a result refreshes the latest data. Not, really a solution though.

For On-Premise users – we can resolve the warning message by adding the Excel file as a trusted location in the Excel Services Application; however, the Excel Service Service Application is not available in SharePoint on-line 

I guess the only viable solution is to for a user to manually open the Excel sheet(s) and do a refresh all and then save the worksheet back into SharePoint; thus, updating all the Power Pivot Charts.

I will let you know if there are any updates on when or if the Excel Service Application is available so we could add worksheets into the trusted locations.

There is a good blog article here which could be of help should you run into a similar issue:

This blog post was originally written in November 2013 – The post was copied from my old blog (Which is no longer active) site to here

So, I have been working on a project for a customer who wanted to Target Audience individual list items in SharePoint…. Simple task, right?

I allowed the management of content types on my list and added in the ‘Target Audience’ site column which is available to us out of the box.  This bit all works fine.

The actual problem arises when you start to audience target the list items.

When you audience target list items, the audience appears as a GUID in the list view (as below)

Basically, if the first item in the list has an audience, then ALL the items appear as GUIDS.
As soon as you remove the audience from the first list item, the audience’s name loads correctly i.e., doesn’t display the GUID.

I did a bit of further reading on this, and a lot of people have said this issue is by design, but surely it can’t be a design issue. No one wants to see a GUID in the view.
I found this issue is evident on all on-premise versions of SharePoint (2007, 2010 and 2013) and SharePoint on-line.

Having contacted Microsoft about the issue; they confirmed that this issue is a Bug in SharePoint and there is currently no fix for it 
The current workaround is to remove the audience from the first item :S

Awaiting a response as to when it will be fixed and why it hasn’t been fixed since 07. Until then, I guess we have to live with it.

**UPDATE – Microsoft responded to my reply as to why this issue had not been fixed since 07 & when they plan to have a fix for it.  Below is the response.

“This bug will not be fixed. SharePoint uses XSLT stylesheets to render list views. A list is rendered as an HTML table and the value of a field is rendered into the appropriate cell of the table by a simple XSLT template from the fldtypes.xsl file located in %ProgramFiles%\Common Files\Microsoft Shared\web server extensions\14\TEMPLATE\LAYOUTS\XSL. And this issue relates to the .xsl file. ”

So, there you have its folks, an unfixable bug in SharePoint  –

I will have a play around to see if we can have another workaround to this issue – where we don’t have to remove the audience from the first list item. Will keep you posted!

 **UPDATE – Temporary Fix to Audiences appearing as GUID

Hi all, finally got around to finding a temporary fix to this problem.

As the GUID on Target Audiences only appears on the first item in the list, we need to find a way of hiding the first item… Having tried to use filters on the view to hide the first ID – the problem still remained, (because the first item in the list will be item 2 If you hide item 1)

A Solution that worked for me was adding the snippet web part to the actual list and then adding a bit of code to it. See below.

1. Create a bogus item as the first item in the list. Make sure that this item has an ID of 1. (So, it has to be the first item in the list)

• Edit the list by selecting the cog in the corner and Edit Page

• Insert the snippet web part on top of the list and insert the following code:

 <style>
.ms-listviewtable > tbody > tr:first-child{
   display: none;
}
</style>

     4.Save the page

     5. Voila

The snippet is just basically just hiding the first item in the list.

*I strongly recommend you try and test this before you insert any code onto a PROD environment.