Powerapps - Creating Autofill dropdown fields from SharePoint List

Scenario:

You have SharePoint Online list with four columns as given below and build a Canvas PowerApp to show the unique values of Country column in Country dropdown, and apply filter on State dropdown to only show those states from State column which are selected in Country dropdown. likewise do same thing with City dropdown. At last, when Country, State and City dropdowns are selected in respective dropdowns. Click on Show button to show/render HTML code from Description column.

Create SharePoint online list with name "Countries" using below columns and values.

Column Name    |    Type 

Country    |    Single line of text

State    |    Single line of text

City    |    Single line of text

Description    |    Multiple line of text + "Enhanced rich text with pictures, tables, and hyperlinks" enabled

and you entered the list items as below

Country     |    State    |    City    |  HTML

USA    |    Illinois    |    Chicago    |    "Hello Chicago - Click here"  

USA    |    Michigan    |    Kalamazoo    |    "Hello Kalamazoo - Click here"  

CANADA    |    Ontario    |     Oakville    |     "Hello Oakville - Click here"

Now follow below steps and create Canvas app.

1. Login to https://make.powerapps.com

2. Click on "Apps" option from left hand side and then click "Canvas"

3. On design screen, click on "Data" from left navigation and "Add data". You can search "SharePoint" data source to make data connect with above SharePoint list called "Countries"

4. On screen, add first dropdown box name ddCountry and use below function:

        fx=Distinct('Countries', Country)

        This will display all unique values from Country column in ddCountry dropdown

5. Add second dropdown box name ddState and use below function:

         fx=Distinct(Filter('Countries', Country = ddCountry.Selected.Result), State)

        This will display all unique values from State column in ddState dropdown

6. Add third dropdown box name ddCity and use below function:

        fx=Distinct(Filter('Countries', Country = ddCountry.Selected.Result, 

          State = ddState.Selected.Result), City)

        This will display all unique values from City column in ddCity dropdown

7. Add HTML text box on screen and use below function:

        fx=Substitute(varURL, "/sites/", "https://<yourdomain>.sharepoint.com/sites/")

        User this method in case you want to replace the links from Description column

8. Add button name btnShow and use below function:

        fx=Set(varURL,First(Filter('Countries', Country = ddCountry.Selected.Result, 

        State = ddState.Selected.Result, City = ddCity.Selected.Result).Description);

9. Done

On-premise data gateway for Power App, Power BI, Power Automate and Azure Logic Apps

 On-premise data gateway

There are many situations where you will use the on-premise data from different sources to be presented or modified or added from cloud applications like PowerApp, Power BI or Power Automate. On-premise data could be on SQL instance etc. At present there is no direct possible way to transfer the on-premise data to could directly and securely. 

On-premise data gateway allow safe data transfer between on-premise data source locations to cloud services/applications like PowerApp, Power BI or Power Automate. Idea behind using the on-premise gate way to keep the on-premise data on organization network and use online

On-premise data sources could be SQL server, SQL server analysis services, files or SharePoint and other data sources. Similarly Could applications which can use on-premise data could be PowerApp, Power BI, Power Automate, Azure Logic App and analysis services.

Installing and configuring On-premise data gateway

You can click here and visit Microsoft location to get related information like minimum requirement, where to download & how to configure the on-premise data gateway. I will try to cover few topics before planning the on-premise data gateway for your organization.

A) On-premise data gateway naming convention or usage:

You can install on-premise data gateway on your local computer or virtual / dedicated server having consistent network connectivity. You can use same data gateway connecting many data sources. It is good to have generic name used across your organization instead of using gateway name specific to any data source or group if people or team.

B) On-premise data gateway governance:

You can manage permission to gateway as Admin, Can use & Can use and Share. With Can use permission - one can only be able to use the gateway, Can use and share permission - use the gateway as well as share among other team members and Admin can delete gateway and provide access to other team members.

C) Rename or Delete On-premise data gateway:

You can delete the gateway by visiting the Data section in PowerApp online provided you have Admin permission. In addition, We do not see the direct options to rename the on-premise data gateway but deleting the on-premise data gateway and then recreating new one could be one option. If on-premise data gateway has used in many places and you have production usage, it is not advisable to follow this step. Below steps can be considered:

• Take snapshot of permissions provided to users with current gateway. You will have to grant same level of permissions to users with newly created on-premise data gateway
• Take another snapshot of connections where you have used this on-premise data gateway. You can visit PowerApp > Data section > Gateways > select current gateway > go to details > connection section
• Optional step: You can send communication to your users about decommission of current gateway
• Delete on-premise data gateway
• Create new on-premise data gateway using correct naming convention and provide same level of permission to users as per step 1.
• Visit all connections as per step 3 and update the connect with new on-premise data gateway & save it.
• That is it. It take little while to reflect the changes. In my case, it tool 15 to 20 minutes.

D) New version of gateway:

Microsoft will be providing upgrades to on-premise data gateway. You can visit here to see how to upgrade the gateway.

Unable to delete SharePoint Online site

 There could be many reasons if you are not able to delete the particular SharePoint Online site from Office 365 tenant. I would like to discuss below error messages:

"The site can't be permanently deleted because it's connected to an Office 365 group"

This issue mostly identified with SPO team sites, as modern team site may associated with office 365 group in backend. Before deleting the SharePoint Online site you need to delete the office 365 group. Pease see below steps

1) User PowerShell to connect to SPO Service.

Connect-SPOService -Url https://<SPO Admin URL>

2) Run below command to identify the associated group for SPO team site

Get-SPOSiteGroup -Site https://<SPO Site Collection URL>

3) Delete office 365 group

Remove-UnifiedGroup -Identity "<Group Name>"

4) Delete SharePoint online site

 Remove-SPOSite -Identity https://<SPO Site Collection URL> -NoWait

"This site cannot be deleted because it is included in an eDiscovery hold or retention policy"

In most of the cases, this has been seen that organization may have applied the retention policy and as a result owners can not delete the SharePoint Online site. You can verify the retention policy at below location:

Office 365 portal > Security Compliance > Data governance > Retention

Recommendation is not to remove the retention policy for entire organization but you can add your site in exclusion list of retention policy and then try to delete the SharePoint Online site.

In addition, admins also cannot delete a site that is under a retention policy. In order to site be deleted, the site must first be excluded from the retention policy locations. You can do so by adding an exception through the UI in the Security and Compliance Center (SCC)>Data Governance>Retention>the specific policy or through PowerShell. 

Using PowerShell, you must first connect to the SCC and then use the cmdlet  below

Set-RetentionCompliancePolicy -Identity "<Name of exiting Policy>" -AddSharePointLocvationException "https://<SPO Site Collection URL>

Except External Users and AllUsers default groups?

This is a rear question, but sometime your organization may require to control the security of entire SharePoint online site and disallow default "Everyone",  "Everyone Except External Users" or "Allusers" to be used by respective site owners.

Currently, it is not possible to disable this group from office 365 tenant but you can hide this group to be visible so that when site owners are providing permissions to users on SharePoint online site, they actually can't see or search it.

You can use below PowerShell command to hide this group:

Set-SPOTenant -ShowEveryoneExceptExternalUsersClaim $false -ShowEveryoneClaim $false -ShowAllUsersClaim $false

For more information, you can refer below article: https://docs.microsoft.com/en-us/powershell/module/sharepoint-online/set-spotenant?view=sharepoint-ps

What will happen to exiting SharePoint online sites (after you hide this group)?

It won’t effect those currently using "Everyone",  "Everyone Except External Users" or "Allusers" claims. However, if site owner delete Everyone (for example) from the members group, they will not be able to re-add it because it won’t show up in the people picker. (Unless you set that parameter back to $true for the entire tenant.)

Microsoft Office 365 learning pathways solution

 About

Microsoft Office 365 learning pathways is learning solution to help any organization in learning office 365 and its application's working. In addition, it helps to increase the daily usage of these online services and drive user adaptation. It empower your users to do things. If your organization policy allow the customized solution implementation then this could be learning solution for your users.

You can find all related information about this learning solution, how to configure, usage and troubleshooting from Microsoft locations below:

Reference:

https://docs.microsoft.com/en-us/office365/customlearning/

https://docs.microsoft.com/en-us/office365/customlearning/custom_provision

https://docs.microsoft.com/en-us/office365/customlearning/custom_exploresite

https://docs.microsoft.com/en-us/office365/customlearning/custom_troubleshooting

In this blog we would like to discuss some more facts and finding about implementation and usage of this learning pathway solution.

Its benefits:

• It's free solution, no additional fee
• Provide modern experience to end user while learning and training
• Solution can be modified based on your organization need. For example, you can hide Microsoft provided play list and create your own
• Very easy to create various play lists based on your relevant scenario & target to specific audiences
• Offer on-demand training for your office 365 users
• It has custom web part which offer dynamic feed and content
• Very easy to deploy and configure using provisioning service
• Center place of all training and learning material organized by category or application wise.

Additional Information:

• Require tenant admin rights to deploy this solution on tenant.
• "App Catalog" site collection needed/created before you configure the leaning pathway solution. 
• Make sure you are site collection administrator of "App Catalog" as you will use "Apps for SharePoint" section to upload custom learning pathway app
• Require modern communication site to configure learning pathway apps/web part. This is going to be framework for custom solution. The communication template works best with this custom 365 learning pathways solution

Helpful Links:

• Driving Adaptation Blog: Microsoft is updating the latest new or information about this solution on "Driving Adaptation Blog" site. You can follow and also get latest feed
• Feedback and support: Microsoft also offer users to report any issues, ask questions, provide suggestions or provide feedback about this learning solution at "Feedback and Support" site

SharePoint Online list connection issue with Tableau desktop app

 About

Tableau desktop application is being used most popularly in various organizations for data analysis or reporting purposes. It allows connection between SharePoint Online list to get the data and display. This article will cover connection issue between SharePoint Online list and Tableau Desktop application. 

"An error occurred while refreshing tasks." 

"You must either set an auth cookies or specify a username and password to connect to SharePoint Online. Unable to cerate extract"
OR
"Unrecognized response when getting SharePoint online security cookies, please check your credentials."

If you connect Tableau desktop app with SharePoint online list and able to create extract, publish to tableau server and finally save the extract. No issue reported. But once you reopen the saved extract file and try to refresh the connection it gives you connection issue with below error message.

"You must either set an auth cookies or specify a username and password to connect to SharePoint Online. Unable to cerate extract"

Troubleshooting:

• Make sure your are using correct version of drivers both on client side and server side. You can find latest driver information from Tableau site. click here
• You can use Fiddler or WireShark apps to capture and compare the traffic data while connecting to SPO list when it was first working and then when it is not working. 
• You can try to login to Tableau client desktop app and as well as web app in non-cached environment (using browser in private mode) and to see if issue is limited to desktop app or web app also have same trouble.
• Try using same account email address to login to SharePoint online list directly to see if you can connect without Tableau app 
• Resolution: If you are using Tableau version 2019.4 (or below) desktop client app, then you can consider upgrading it to higher version as this has been seen that if you try to open already saved Tableau extract file it will not allow you to re-enter the credentials and give above error messages. In higher version like 2019.4.3 (or latest) desktop client you can see new popup feature to re-enter the credentials and connect to SharePoint Online list again.

Reference:

You can visit below articles to know How to connect to Tableau with SharePoint Online list

https://help.tableau.com/current/pro/desktop/en-us/examples_sharepoint_lists.htm

https://www.tableau.com/support/drivers?_fsi=IXoDeruy&_ga=2.65582054.973423501.1589759769-1324713014.1589759769&_fsi=IXoDeruy

Highlighted Content web part missing Page Properties

Using SharePoint online modern pages, when you try to add Highlighted Content web part and try to filter the content by column values or page properties, it does not show you filter option to choose "Page properties" and further not allow you to choose any columns. This is not an issue but you need to choose the different source under "Content" section. 

Please follow below steps:

1. Add Highlighted Content web part on modern SharePoint page.

2. Go to web part properties 

3. Under section "Content", choose "The page library on this site"

4. Under section "Filter and sort", choose filter "Page Properties"

5. Now you can see option to select property name and text box to add the values.

For more help and information about Highlighted content web part you can also visit : https://support.microsoft.com/en-us/office/use-the-highlighted-content-web-part-e34199b0-ff1a-47fb-8f4d-dbcaed329efd