What happened to the Web URL link in Power BI? With the latest update to Power BI Desktop, Microsoft made some UI changes. If you are looking for the Web URL option, make sure your data grid / table is selected, click the Format visual button, expand Cell elements, at the bottom you’ll find Web URL. Switch it to On and you’ll be ready to go!
Below are all the steps required to add your company holidays to your Outlook calendar. This is a simple approach that can easily be updated to pull the holidays and dates from other data sources like Exel, SharePoint, Dataverse…
Overview of the flow that will be created:
All of the holidays (events) will be stored in an array using a compose action.
[
{"Event":"Holiday - New Years","Date":"01/01/2022"},
{"Event":"Holiday - MLK Day","Date":"01/17/2022"},
{"Event":"Holiday - Presidents Day","Date":"02/21/2022"},
{"Event":"Holiday - Memorial Day","Date":"05/30/2022"},
{"Event":"Holiday - Juneteenth Day","Date":"06/20/2022"},
{"Event":"Holiday - Independence Day","Date":"07/04/2022"},
{"Event":"Holiday - Labor Day","Date":"09/05/2022"},
{"Event":"Holiday - Thanksgiving Day","Date":"11/24/2022"},
{"Event":"Holiday - Day After Thanksgiving","Date":"11/25/2022"},
{"Event":"Holiday - Christmas Day","Date":"12/26/2022"}
]
After that, a parse JSON event will be used to make the events available in the apply to each action. Note: after the parse JSON action is added to the flow, click the generate from sample button and paste in the holidays JSON from the step above.
Next, add an apply to each action to the flow. In the action, the output box will hold the body of the parse JSON action.
The convert time zone action is used to standardize the date format. For the base time use the Date field from the parse JSON action.
The last item that needs to be added is the outlook create event action. Note: for the End time property a formula is used: addDays(body(‘Convert_time_zone’),1)
Save the flow and run it.
Edit/Update –
For the End time value, you can input this expression: addHours(body(‘Convert_time_zone’),23) . If you set Is all day event to Yes, you’ll need a timespan that covers most if not all of the day.
Also, if you want to set Is reminder on, it must be input as minutes, so 17 hours would be 1020 minutes.
Unable to process template language expressions for action 'Apply_to_each_sftp_file' at line '1' and column '30517': 'The template language expression 'body('List_files_in_folder')?['body']' cannot be evaluated because property 'body' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.From one day to the next one of my flows stopped working with the error above. The flow is super simple:
Get files from an SFTP folder
Loop through the files
Copy file to another location
Delete file
Looking at the apply to each step I noticed body element was referenced. This might have been related to how I set up the loop or the autogenerated action.
Action the flow was unhappy with:
Updated the flow and it started working:
Note how the ?[‘body’] element is missing from the second picture.
Fix?
Created a new Apply to each loop by first setting a variable to the name of the file I’m looping on, then added my other actions. There might be a better way to fix this, but for now, this is what works.
Using Flow to get or check for files in a large SharePoint library can be a little tricky. If you are sure your library will always stay under 5,000 items the Get Files (properties only) Flow action is a quick n’ simple approach to use. When your library crosses over the mythical 5k mark or somewhere in that neighborhood, the Get Files action will fail to return results without warning. What I’m outlining below are other options when working with large libraries.
Setup:
Option 1: Get Files using an Odata filter query
downside: only use this in small libraries
Result:
Option 2: use the SharePoint API
downside: the lack of transparency from Microsoft related to how often data is crawled. 
Result:
Option 3: use the SharePoint API along with a filter action on the library. This option does require that you have metadata set up on the library to filter on and there is not a wildcard / contains option. The filtered value needs to be exact.
downside: you will need to set up your metadata ahead of time or create it after the fact then backfill.
Result:
The more I learn about Flow and SharePoint Online, the more I’m starting to like Option 3 when doing a lookup type of search. SharePoint Search is an extremely powerful tool if the search index is fresh.
How do you download a file from a SharePoint Online library using Python?
Update – If you scroll to the bottom, I’ve outlined another approach that uses a username and password to connect via the SharePlum library.
Items needed to run the script in this example:
Office365 Rest Python Client library:
https://pypi.org/project/Office365-REST-Python-Client/
SharePoint App Only Client Id and Secret:
Microsoft documentation:
https://docs.microsoft.com/en-us/sharepoint/dev/solution-guidance/security-apponly-azureacs
You can create an app principle that is limited to a single site, list, library, or a combination of them:
https://piyushksingh.com/2018/12/26/register-app-in-sharepoint/
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.files.file import File
app_settings = {
'url': 'https://YOURtenant.sharepoint.com/sites/somesite/',
'client_id': '12344-abcd-efgh-1234-1a2d12a21a2121a',
'client_secret': 'Oamytacohungry234343224534543=',
}
context_auth = AuthenticationContext(url=app_settings['url'])
context_auth.acquire_token_for_app(client_id=app_settings['client_id'], client_secret=app_settings['client_secret'])
ctx = ClientContext(app_settings['url'], context_auth)
web = ctx.web
ctx.load(web)
ctx.execute_query()
response = File.open_binary(ctx, "/Shared Documents/Invoice.pdf")
with open("./Invoice.pdf", "wb") as local_file:
local_file.write(response.content)
If the above script does not work, step back and ensure you are connected to the site. The following script connects to a site and outputs its title. This is useful to validate that a site connection can be made.
from office365.runtime.auth.authentication_context import AuthenticationContext
from office365.sharepoint.client_context import ClientContext
from office365.sharepoint.files.file import File
app_settings = {
'url': 'https://YOURtenant.sharepoint.com/sites/somesite/',
'client_id': '12344-abcd-efgh-1234-1a2d12a21a2121a',
'client_secret': 'Oamytacohungry234343224534543=',
}
context_auth = AuthenticationContext(url=app_settings['url'])
context_auth.acquire_token_for_app(client_id=app_settings['client_id'], client_secret=app_settings['client_secret'])
ctx = ClientContext(app_settings['url'], context_auth)
web = ctx.web
ctx.load(web)
ctx.execute_query()
print("Site title: {0}".format(web.properties['Title']))
SharePlum connection example using a username and password to connect to SharePoint Online. More details about SharePlum can be found here: https://github.com/jasonrollins/shareplum
from shareplum import Site
from shareplum import Office365
sharepoint_url = 'https://YOURtenant.sharepoint.com/sites/spdev'
username = 'You@YourDomain.com'
password = 'Password'
authcookie = Office365('https://YOURtenant.sharepoint.com',
username=username,
password=password).GetCookies()
site = Site('https://YOURtenant.sharepoint.com/sites/DEV/',
authcookie=authcookie)
sp_list = site.List('Your List')
data = sp_list.GetListItems('All Items', row_limit=200)
If you get this error, you won’t be able to connect with a username and password, and you’ll need to use an App Password.
File “C:\Python311\Lib\site-packages\shareplum\office365.py”, line 80, in get_security_token
raise Exception(‘Error authenticating against Office 365. Error from Office 365:’, message[0].text)
Exception: (‘Error authenticating against Office 365. Error from Office 365:’, “AADSTS50076: Due to a configuration change made by your administrator, or because you moved
to a new location, you must use multi-factor authentication to access ”.”)
I’ve created this post to outline how to upload a large file to a SharePoint library:
https://www.sharepointed.com/2024/03/how-to-upload-a-large-file-to-sharepoint-using-the-microsoft-graph-api/
Scenario:
Each day I have a couple of Azure Runbooks export SharePoint list items and upload them to a SharePoint library. If one of the Runbooks fails, I needed to send an email alert that something went wrong.
Basic logic:
If files created today in SharePoint <> X, send an email.
The easy solution would have been to loop through the files, check their created date, increment a variable, then make a condition statement.
More-better way:
Run flow daily at 6:00 PM
Send an HTTP request to SharePoint to get files
Parse the response
Condition statement
— if true, send an email
Uri text from the HTTP call:
_api/search/query?querytext='Path%3Ahttps%3A%2F%2Fsharepointed.sharepoint.com%2Fsites%2Fsitename%2Fsubsite%2Fexports%2F*%20LastModifiedTime%3Dtoday'
Parse JSON schema
{
"type": "object",
"properties": {
"odata.metadata": {
"type": "string"
},
"ElapsedTime": {
"type": "integer"
},
"PrimaryQueryResult": {
"type": "object",
"properties": {
"RelevantResults": {
"type": "object",
"properties": {
"TotalRows": {
"type": "integer"
},
"TotalRowsIncludingDuplicates": {
"type": "integer"
}
}
}
}
}
}
}
Edit –
If you want to search for files created before or after a date, you can adjust the API like this:
and created %3E 2021-12-12T19:07:51.0000000Z
This will fetch any files created after Dec 12th 2021.
The unicode for greater than is %3E and less than is %3C
I created a simple Power BI report to pull data from a SharePoint list and quickly ran into a problem with the Person or Group column type.
Error:
Expression.Error: We cannot convert the value “” to type Table.
Details:
Value=
Type=[Type]
In the report, I was trying to expand a Person or Group column, but not all of the field values were populated.
Here is the fix:
let
Source = SharePoint.Tables("https://taco.sharepoint.com/sites/food", [Implementation="2.0", ViewMode="All"]),
#"abcc-b8fe-4b23-be01-abc5f2c3320c" = Source{[Id="abcc-b8fe-4b23-be01-abc5f2c3320c"]}[Items],
#"Expanded Assigned User2" = Table.TransformColumns(#"abcc-b8fe-4b23-be01-abc5f2c3320c", {{"Assigned User", each if Value.Is(_,type list) then _{0} else [title = "not assigned"], type record}} ),
#"Expanded Assigned Analyst" = Table.ExpandRecordColumn(#"Expanded Assigned User2", "Assigned User", {"title"}, {"Assigned User.title"})
in
#"Expanded Assigned User"
I’m in the process of reorganizing a document library and wanted to store all of the documents in alphabetical folders. Yes, I’m using metadata, but I’ve passed the magic 5,000 item threshold and want to rearrange the library and leverage a rich search experience.
So, using PowerShell, how do you create a bunch of folders going from A to Z?
$siteURL = "https://sharepointed.sharepoint.com/sites/parent/child"
$conn = Connect-PnPOnline -Url $siteURL -Credentials (Get-Credential) -ReturnConnection
try{
(65..(65+25)).ForEach({
$xy = [char]$_
Add-PnPFolder -Name $xy -Folder "/mylibrary" -Connection $conn
})
}
catch{ Write-host -f Red "Error:" $_.Exception.Message}
More information about creating folders using ASCII:
https://devblogs.microsoft.com/scripting/use-powershell-and-ascii-to-create-folders-with-letters/
Out of the box, if you upload a document to SharePoint and tag metadata to it, that data is attached to the document properties. This is the case for most all column types you create in a library, and on random occasions, Document IDs are copied.
If you are working with SharePoint on-prem you can disable this functionality at the SPWeb level, but that’s not the case with SharePoint Online.
on-prem code example:$web = $ctx.Web
$web.ParserEnabled=$false
$web.Update()
Example of what I was seeing in my sample library:
Create a new library titled DocLib
Added a text column titled TacoFiller
Uploaded an Excel file, then populate TacoFiller with the word Beans
Downloaded the file and look at the properties.
File –> Info –> All Properties (Excel o365)
Notice that property TacoFiller is set to Beans.
Create a local copy of the file, then upload it to DocLib.
SharePoint has read the TacoFiller property from the Excel file and applied the data to the column in the library.
Short of doing a document migration, I have NO idea why you’d want this enabled out of the box. If anything, this should be disabled, then allow admins to enable as needed. /rant
TL;DR / how-to disable this functionality:
First, you can only disable this at the library level!
#Load SharePoint CSOM Assemblies
Add-Type -Path "C:\Code\DLL\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Code\DLL\Microsoft.SharePoint.Client.Runtime.dll"
#Config Parameters
$SiteURL= "https://YourSite.sharepoint.com/sites/spdev/testsite"
$ListName="DocLib"
#Get Credentials to connect
$Cred = Get-Credential
Try {
#Setup the context
$Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
$Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.UserName,$Cred.Password)
#get list sharepoint online powershell
$List=$Ctx.Web.Lists.GetByTitle($ListName)
$Ctx.Load($List)
$Ctx.ExecuteQuery()
Write-host "Parser Disabled value " $List.ParserDisabled
$List.ParserDisabled = $true
$List.Update()
$ctx.ExecuteQuery()
}
Catch {
write-host -f Red "Error:" $_.Exception.Message
}
A couple of notes about the script.
You can download the SharePoint DLLs from Microsoft: link
In my tenant, using Get-Credential requires that I use my company email address and an App Password. This may/not be the case with your tenant. App Password info: link
And… after running the script, upload another copy of the Excel file to the library.
The TacoFiller property is no longer being extracted from the Excel file!
I’d like to thank Abhijit Rajurkar for helping with this!
There might be another way to track down tables but here is how I navigate to them.
Navigate to: make.powerapps.com
From the top-right of the page select the environment you want to work in.
In the left-nav, click on Tables, then select the table you want to work with.