Get Link to Channel: Private Teams Channel

 I've just struggled with trying to 'Get link to channel' for a Private Channel in Teams. I typically recommend against using private channels in Teams, and perhaps this is one reason. 

Not sure why, but the 'Get link to channel' functionality doesn't exist for Private Channels like it does for Public Channels. 

Even more frustrating, when you do finally find the link to the Private Channel, it doesn't work for a button embedded in a SharePoint page within Teams tab.

Luckily, with a few simple deletions, you can get this to work.

Step 1: Click the three dots at the top right of Channel 'Posts'

Public Channel Menu:

Private Channel Menu:

For the Public Channel, your job is done - click the 'Get link to channel' option and copy the link. It will look something like this:

Get Link to Channel Link

https://teams.microsoft.com/l/channel/19%3randomstringguidbtg1%40thread.tacv2/ChannelName?groupId=GUIDwillbehere&tenantId=GUIDwillbehere

For the Private Channel, click 'Open in SharePoint' first, then click the Teams icon (you won't be able to right click > copy link, so will have to actually click the link to open. This will open a brower tab initially, with an option to open Teams - cancel the dialog box. 

Copy the URL from the web browser. It will look something like this:

Open in Teams Link

https://teams.microsoft.com/dl/launcher/launcher.html?url=%2F_%23%2Fl%2Fchannel%2F19%3Aguid%40thread.tacv2%2FChannelName%3FgroupId%3guid3%26tenantId%3guid&type=channel&deeplinkId=guidDl=true&msLaunch=true&enableMobilePage=true&suppressPrompt=true

https://teams.microsoft.com/dl/launcher/launcher.html?url=/_%23/l/channel/19:2fe074ff85f149d6b02db2d8e2c2d708%40thread.tacv2/Emerging%2520Leaders?groupId%3De1ebf9f0-c485-49b1-bc91-98f1cc7a1dd3%26tenantId%3D8a587731-94b7-42f0-80c3-955f55452f65&type=channel&deeplinkId=7a17c76e-7862-47c4-a91f-7c0b455aac5e&directDl=true&msLaunch=true&enableMobilePage=true

Step 2: Edit link

We want to make this link follow the same format as our first 'Get link to Channel' link. Let's compare them side by side: 

Get Link to Channel Link

https://teams.microsoft.com/l/channel/19%3randomstringguidbtg1%40thread.tacv2/ChannelName?groupId=GUIDwillbehere&tenantId=GUIDwillbehere

Open in Teams Link

https://teams.microsoft.com/dl/launcher/launcher.html?url=/_%23%/l/channel%2F19%3A2fe074ff85f149d6b02db2d8e2c2d708%40thread.tacv2%2FChannelName%3FgroupId%3DguidtenantId%3D8a587731-94b7-42f0-80c3-955f55452f65&type=channel&deeplinkId=7c37e892-9b61-48f2-8fba-f009ebf8b1e2&directDl=true&msLaunch=true&enableMobilePage=true&suppressPrompt=true

Differences

You'll notice the 'Get Link to Channel' link is just a simpler version of the 'Open in Teams' link. To get the 'Open in Teams' link to work from SharePoint page embedded within Teams tab, simply delete the parts in bold below:

https://teams.microsoft.com/dl/launcher/launcher.html?url=/_%23/l/channel%2F19%3Aguid%40thread.tacvChannelNamegroupId%3guid3%26tenantId%3guid&type=channel&deeplinkId=guidDl=true&msLaunch=true&enableMobilePage=true&suppressPrompt=true

to end with a link that resembles the 'Get Link to Channel' link:

https://teams.microsoft.com/l/channel/19:31616bb9ae134f9ab605c0ac9635c2a1%40thread.tacv2/ChannelNamegroupId%3De1ebf9f0-c485-49b1-bc91-98f1cc7a1dd3%26tenantId%3D8a587731-94b7-42f0-80c3-955f55452f65&type=channel&deeplinkId=d2990055-682c-4183-857f-50a1a8b51087

Get Data from SharePoint Files into Power BI

Today I'm finally going to let you in on one of my best kept secrets - how to get data from SharePoint files into Power BI without referencing your local C:\ (or D:\ or E:\) drive. This will ensure that you can schedule refreshes on the SharePoint files you are using with Power BI.

How to Get Data from Excel or CSV file on SharePoint into Power BI (without a gateway)

Step 1: Get SharePoint filepath URL

In order to schedule a refresh in PowerBI.com on files stored in SharePoint, you need to use the https:// URL. There are two main methods I use to grab this URL.

Method 1: From Teams or SharePoint

For this method to work, you must open the file in SharePoint. 

If you're starting in Teams:

• Click the 'Open in SharePoint' button on the ribbon of the Files tab:

• This will take you to the SharePoint document library for that file. Continue following the steps below for SharePoint.

If you're starting in SharePoint:

• Locate the file you want to import into Power BI.
• Click the three dots next to the file
• Select 'Details'
• A new pane will open on the right hand side of your screen - scroll ALL the way to the bottom
• Click the two sheets of paper next to the word 'Path' to copy the filepath
• That's it - you'll use this to get data into Power BI

Method 2: From Excel or desktop app

This method is easy if you've already got the file open in the desktop app. 

• Click File > Info
• Click the 'Copy path' button
• This URL will have a ?web=1 after it that you'll need to delete:
• https://excelwithallisonmvp.sharepoint.com/sites/Training/Resources/PowerBIEssentials/Adventure%20Works%20Product%20Categories.xlsx?web=1
• Keep only the main part of the URL:
• https://excelwithallisonmvp.sharepoint.com/sites/Training/Resources/PowerBIEssentials/Adventure%20Works%20Product%20Categories.xlsx
• That's it - that's what you'll need to get data into Power BI

Step 2: Get data into Power BI (use Get data from Web)

Even though your data source is likely to be an Excel or CSV file, we are going to use the 'Get data from Web' option to get the data into Power BI.

This is because we have an https URL that points directly to our file. The last part of the URL is the filetype (such as .xlsx) and Power BI will use that to automatically use the correct functions to import the data correctly.

• Click Get Data > Web
  
  
• Paste the URL from Step 1 (make sure you have deleted the ?web=1 if using Method 2)
  
  
  
• Click OK
• If you haven't used this data source previously, you'll be prompted to enter credentials. 
• Choose Organizational Account
• Sign in with the same account that has access to the SharePoint file.
  
  
  
  
  
• Click Connect
• That's it! 
• The next screen you see will depend on the file type. For an Excel file, I see a navigator window asking which sheets or tables I want to import. 
• Continue importing the data as you normally would for that filetype.
  
  
  
  
• When you publish this to PowerBI.com you'll be able to schedule a refresh using your current credentials - no Gateway needed!

Tokyo Olympics: Automate Twitter Posts

 

If you follow me on Twitter (@ExcelAllison) you may have noticed I've been playing around with Power Automate and sending out automated Tweets throughout the Olympic games. I've really been enjoying following my two 'home teams' and watching the medal tallies tick up, with near-instant alerts. 

Power Automate

Power Automate is a fantastic tool and really versatile in what it can do. It doesn't require any formal coding, though there is a bit of a learning curve at first and it does help to have some basic understanding of logical functions. However, if you put in a few hours a week to get up to speed with Power Automate, it could save you 2-3 days a month that you'll no longer have to spend on repetitive administrative tasks. I'm not exaggerating - there are studies and the time saved is real!

Objective

The goal of this exercise was to a) demonstrate the capability of Power Automate and b) drive traffic to my Tokyo Olympics 2020 Power BI report and blogs. I wanted to take advantage of the live data streaming throughout the Olympic games and engage with it on some level. I'm not very good at Twitter, but I knew that Power Automate has a Twitter connector so thought I'd give it a go - it's the perfect public forum for testing and showcasing the Power of Power Automate and Power BI together.

Born in the US but living in NZ, I wanted to keep up with both teams throughout the Olympics. NZ tv have been doing a good job of keeping us posted on the Kiwi results, but getting instant updates on the US athletes required a bit more effort. 

Key Components

Okay, so let's lay out the concept from start to finish: 

1. Connect to live dataset using Power BI
2. Create explicit measures for the key metrics we want to track (in this case total medals earned by NZ and total medals earned by US)
3. Publish Power BI dataset
4. Setup automated, scheduled refresh on Power BI dataset
5. Create dashboard and manage alerts for the two key metrics
6. Create flow in Power Automate that is triggered by the alert

We've already completed steps 1-5, so this post is all about step 6. As I started working through this process, I further developed step 6, thinking about what I wanted to accomplish with my flow and what my goals were. I wanted to:

• be alerted when my favorite teams won a new medal
• understand what they had won or what was happening in the Olympics
• share that joy with others

Power Automate has lots of connectors for alerts, notifications and information transfer, but Twitter seemed like the perfect platform for sharing the joy with others and also for figuring out what else was happening with the team.

I created two identical flows - one called 'Congrats Team NZ' and one called 'Congrats Team USA'. To simplify this post, we'll focus on the Congrats Team USA post (I used this one for testing purposes as USA got a medal before NZ and has earned more medals than NZ, so this flow has given me more opportunity to refine and test the process.)

Congrats Team USA

 After a few iterations and learning a bit about Twitter and the Twitter connection, I have developed the 'Congrats Team USA' flow to complete the following tasks: 

1. Start whenever a new medal has been earned by US (as triggered by alert in Power BI dashboard)
2. Search for Tweets from @TeamUSA on Twitter - limit search to 1 latest Tweet
3. Retweet that Tweet (hope here is that it's related to the medal they've just earned)
4. Post new Tweet - apparently this can't be the same Tweet over and over (and I'm sure my followers would get tired of it too) so I developed a list of inspirational quotes to add to this Tweet. In order to post the new Tweet, Power Automate will:
1. Go to the SharePoint list of quotes, find the next in sequence and add that text to the Tweet
2. Update the sequence of all quotes in the SharePoint list to move all items forward one place in line (getting ready for the next run of the flow with a new quote)
3. Post the Tweet on Twitter

Twitter Rules

There are a few restrictions that Twitter and/or Power Automate place on your tweets that might be common causes for failure, so double check you've got all these in place if you want your flow to succeed:

• 280 characters or less: Tweets must be less than 280 characters! If you exceed this limit, your flow will fail.
• Avoid duplicates: You can't post the same tweet twice, nor retweet the same tweet, within a certain time period. Ensure your tweets are unique.
• Volume quota: Keep within the limits of the Twitter API 
• # not @: Mentioning users or any reference to @ character will be removed from your tweet, but # and hyperlinks are ok.

How To

Alright, so how do we setup this Flow?

Step 1: Create SharePoint list of Quotes

From Teams or SharePoint, create a New List. Lists are a super cool app that probably deserve an entire post to themselves, but I'll go through the essentials here. I created a new Blank List. 

By default, this list will have a single column called 'Title' that must be single line of text and is a required field.

Click on the Settings cog at the top of the list and choose 'List Settings'

Scroll down to find the 'Title' column and click on the link to edit the column. Untick the required box and Save. 

Add a new column - multiple lines of text and call it 'Tweet Text'.

Add a new column - number and call it 'Sequence'. 

We'll use both of these new columns in our Flow.

Now click on 'Edit in grid view' and add some inspirational quotes or Tweet messages. Remember the character limit! For the 'Sequence' column, start with 1 and increment by one each row. 

Click 'Exit grid view' to save your changes.

Step 2: Create Flow

Navigate to Power Automate and click 'Create' to start a new flow. 

Click 'Automated cloud flow'

Type 'Congrats Team USA' (or fill in your team name).

Trigger: When a data driven alert is triggered

Search for Power BI in the triggers. Select 'When a data driven alert is triggered'. Click 'Create'.

Search Tweets

Click 'New step' and search for Twitter. Add the 'Search tweets' action. At this stage you will be asked to login to your Twitter account to create the connection.

Enter your desired Search text. In my flow I put 'from:@TeamUSA' as my search text. 

Expand 'Show advanced options' and limit the maximum results to 1.

Retweet

Click the 'New step'. Search for and add 'Retweet'. 

From Dynamic Content, add the Tweet Id from the Search Tweets step. This step will automatically get added to an 'Apply to each'. Even though we limited our search results to 1 Tweet, Power Automate still sees it as a list of Tweets, so will Apply the RETWEET to each search result. 

Click the three dots at the top right of the Retweet step and ensure it's using your signed in Twitter connection.

Variables: Quote and NewSequence

Variables are basically a box to hold information. You can then move and reuse this information, or even add and subtract from it. Variables MUST be initialized in Power Automate before you can use them. In this Flow, I created a String variable for 'Quote' and a Float variable for 'NewSequence'. 

I left value blank for both, we'll assign this later.

Get Items (SharePoint List)

Add a new step for 'Get Items'. This will return the values of the columns in a SharePoint list. 

Select your Site Address and List Name that we created in Step 1 from the drop down.

Apply to each SharePoint List Item

Add a new 'Condition' to the Flow. Choose 'Sequence' column from the Get Items step. This will automatically wrap the Condition inside an Apply to each step. Everything we do in here will now be done to EACH item in the SharePoint list. 

We want to find the next Quote in Sequence - our list starts with Sequence = 1, so that's the quote we want. Ensure your Condition looks like the image above: Sequence is equal to 1.

If yes, add a step for 'Set variable'. Set Quote text Variable to 'Tweet Text' column from the Get Items step. Since we're still inside the Apply to each, this will happen for each item, IF the Sequence is equal to 1. If no, we'll leave that blank.

Now add an action for 'Set variable' below the condition, but still inside the Apply to each. We want to shift ALL the items in the list (not just the one we chose for the quote) so that they move 1 place in line and the next list item ends up with Sequence 1 for the next time this flow runs. 

• Set the 'NewSequence' variable equal to value of 'Sequence' column from the Get Items step.
• Add a new step for Increment Variable. Choose the 'NewSequence' variable and type -1 in the value to increment. 
• Add an new step for Update Item (SharePoint list). Select your Site Address and List Name that we created in Step 1. Put the ID from Get Items into the ID field. Put NewSequence variable in the Sequence field. This moves every item one step forward in sequence/line.

Post Tweet

Below the Apply to Each, add a new step at the very bottom of the flow and search for: Post a Tweet.

Type your Tweet text, be sure to include the Quote Text variable in there somewhere and follow the Twitter character limits!

Save & Test

Once you're happy with your Tweet, save it and test when the Power BI alert triggers. You may need to come back to edit and troubleshoot some small errors. Remember - all testing in Power Automate is live to the data connection. You may want to use a test Twitter account or SharePoint list for testing. When you're ready, you can easily update the connections to the live data sources and Twitter accounts. 

Enjoy!