tag:blogger.com,1999:blog-60740704400299163422024-03-04T07:31:50.963-05:00Mike Smith's Tech Training Notes SharePoint, PowerShell and .Net!SharePoint, PowerShell, .Net and other stuff I spend too much time on...
Unknownnoreply@blogger.comBlogger626125tag:blogger.com,1999:blog-6074070440029916342.post-82694229899474035462022-03-18T19:14:00.002-04:002022-03-19T21:52:49.661-04:00ASP.NET Core Middleware to Update Response BodyI needed a quick way to update a mix of static HTML files and Razor views to change a message common to a lot of pages. In the example here I will update the Copyright message in HTML pages.As usual I searched the web and found many examples, none that worked the way I needed (or worked at all!), but I did find an assortment of "bits" scattered throughout StackOverflow that helped me build a Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-39016548033146256742021-07-09T18:32:00.002-04:002021-07-09T18:32:53.244-04:00Power BI BooksI am often asked for book recommendations in my classes. Book choices are a personal thing that varies based on your learning style. So, I rarely recommend any particular book. The books that follow are books that I really like and recommend.Storytelling with Data: A Data Visualization Guide for Business Professionals
This is not a Power BI book. Before you create a report, you should knowUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-10380944727773216062020-08-04T15:30:00.003-04:002020-08-05T16:43:17.334-04:00Merging JSON files using PowerShellLet's say you have a directory full of JSON files and you need to merge them into a single file.Emp1.json { "Name": "Mike", "ID": 123, "Status": "Active" }Emp2.json {  Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-32465200602736400362020-08-03T19:39:00.002-04:002021-01-06T21:29:52.536-05:00How to count lines of code using PowerShellI've got a project... scattered through a number of folders are a bunch of C# files. How many lines of code do I have? (Bragging rights you know...)You could open the project in Visual Studio and use the Analyze, Calculate Code Metrics. But that requires Visual Studio, and does not give me all the options I want. (But in many ways is better! Lines by file and by method are available there.)I Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-51651476206677447812020-04-25T20:13:00.000-04:002020-04-25T20:14:38.680-04:00PowerShell: When does zero equal one? (Length property of a Directory)
You learn the most interesting things when teaching a class, especially when an off the cuff demo goes wrong.
During a simple demo of a calculated or derived column I did this:
For files, the output was as expected:
But for directories it returned this:
While the FileInfo object does have a Length property, the DirectoryInfo object does not. So what should we expect when both files and Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-12593322060830295992020-04-01T12:59:00.000-04:002020-04-01T13:34:19.945-04:00SharePoint vs. Teams vs. OneDrive<!--[if gte mso 9]>
Normal
0
false
false
false
EN-US
X-NONE
X-NONE
MicrosoftInternetExplorer4
<![endif]--><!--[if gte mso 9]>
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-54136073462829483042019-07-22T21:55:00.000-04:002019-07-22T21:55:57.431-04:00SharePoint Date Validation Formula to Prevent Selecting Holidays
We often want to prevent the selection of certain days when users enter tasks or events into SharePoint lists.
I have over 100 Calculated Column and Validation formulas in my book!
Block weekends
This formula is quite simple and uses the Excel WEEKDAY formula to test the day of the week. WEEKDAY assigns a number to each day using a "scheme". The scheme I'll use here is "2" as it putsUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-70629055587137171332019-07-20T22:38:00.002-04:002019-07-20T22:39:06.189-04:00Power BI - Convert a Column into a Comma Delimited list.
When grouping data have you ever wanted to keep a summary of the details? Maybe as a comma delimited list as a single column?
I've put together a sample project here that takes projects and their tasks and groups them by project and summarizes each project by summing project hours and by creating a comma delimited list of tasks.
Here's the source data. It happens to be from a SharePoint list Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-33303378786858088132019-07-20T14:23:00.004-04:002019-07-20T14:23:45.043-04:00The controller for path '/' was not found or does not implement IController #VisualStudio #MVC
The controller for path '/' was not found or does not implement IController
Some error messages are no help at all!
When running in Debug mode Visual Studio reports:
An exception of type 'System.Web.HttpException' occurred in System.Web.dll but was not handled in user code.
Additional information: Execution of the child request failed. Please examine the InnerException for more Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-28158569984906312802019-07-01T17:33:00.000-04:002019-07-31T12:41:48.256-04:00Dynamic Images in SharePoint Document SetsOne of the cool features of Document Sets is that you can customize the "home
page" of the Document Set by adding or editing its Content Type's home page web
parts. The default page includes an image web part that can be customized with
your choice of image. The only problem here is that the image is then used for
all Document Sets created from this Content Type. Here's the default "home
Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-25326856858178931912019-04-30T19:17:00.001-04:002019-04-30T19:22:04.493-04:00SharePoint.Tables – Power BI – Power Query M Functions
The Microsoft DOCS article for SharePoint.Tables says "Returns a table containing a row for each List item found at the specified SharePoint list url." While this sounds like it returns list "Items", it actually returns a list of Lists. It also reads like the URL is supposed to point to a list. Instead, it should point to the site or subsite.
If this was a typical DOCS article, I'd click the Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-21092366831687092902019-04-29T13:41:00.001-04:002019-04-29T14:04:06.660-04:00Three Ways to Load SharePoint Data into Power BI and Excel
Power BI includes a connector/wizard for SharePoint lists that makes is easy to load list content. It works great, but has a shortcoming or two. It first retrieves a list of all of the lists, and then it retrieves all of the list content… even if you only need a small subset of the data.
In this little article I'll show these approaches:
Using the Wizards
Writing your own "M" formulas
Using Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-82755997305304447092019-03-16T14:24:00.001-04:002019-03-16T14:29:40.679-04:00SharePoint Validation Formula Tip – Don't Use IF!
I often see questions about SharePoint validation formulas in the online forums that include IF statements to return True or False. Something like this:
=IF( someCondition , True, False )
=IF( Amount > 100, True, False )
=IF( AND( State = "OH", Amount>100 ), True, False )
The IF is simply not needed!
Simply replace Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-6074070440029916342.post-20189656525585899672018-10-30T21:11:00.001-04:002018-10-30T21:30:12.804-04:00SharePoint Calculated Columns – Convert a Month Name to a NumberHere are three solutions for when you have a column with a three letter abbreviation of a month name, and you want the month number,
Each of the following assume that you have a "Single Line of Text" or "Choice" column named "Month".
A long IF statement:IF([Month]="JAN",1,
IF([Month]="FEB",2,
IF([Month]="MAR",3,
IF([Month]="APR",4,
IF([Month]="MAY",5,
IF([Month]="Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-78408291764126444912018-10-15T15:22:00.001-04:002018-10-17T20:52:10.622-04:00SharePoint Calculated Columns and Validation Formulas – The Book!Available on Amazon!
It only took me a year… and it grew from a little booklet to over 200 pages… but its finally done!
Bought the book? Post any questions, bugs, typos and suggestions to this blog post!
Everything you need to know about SharePoint formulas for SharePoint 2010 to 2019 and SharePoint Online / Office 365!
This book is for the SharePoint “power user” who needs a better Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-44227529415033102982018-09-06T17:22:00.001-04:002018-09-06T17:27:37.032-04:00SharePoint Dates are Always Date AND TimeA Quick SharePoint Date Tip!When you create a Date column you have the choice of Date and Date & Time.
Note the keyword “Format” in that option. Even if you select “Date Only”, your users can still type, or copy and paste, a date and a time and it will be stored as a date and time. But… only the date will be displayed.
Times are a Fraction of a DateTimes are represented Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-2427593076609738602018-04-20T23:59:00.001-04:002018-04-21T00:12:30.546-04:00The Mysterious Semicolon in SharePoint SearchDiscoveries:A semicolon in a search is the same as the keyword "AND".
You cannot uniquely find text that contains a semicolon. (such as ABC;DEF)
A semicolon used as data in a checkbox enabled Choice column causes data and search problems.If you have more info, or know of an escape character for “;” please post a note to this article!";" equals "AND"?If I were to search a SharePoint list for "Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-63172480258411427022018-04-07T13:52:00.001-04:002018-04-07T13:58:11.367-04:00SharePoint TEXT() Function Bug for Scientific NotationIn Excel you can use the TEXT function to display very large and very small numbers in scientific notation.
TEXT([Number],"0.00E+00")
Due to a bug in SharePoint Calculated Columns, the TEXT function adds some extra zeros to the end of the formatted number.
Here’s a workaround…Assuming you want the equivalent of TEXT([Number],"0.00E+00")=LEFT(TEXT(Number,"0.00E+00"),6) &
TEXT(MID(TEXT(Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-14015732887210321722018-03-29T20:40:00.001-04:002018-10-18T14:31:15.102-04:00Shorter SharePoint Calculated Column OR StatementsTested in SharePoint 2010, 2013, 2016 and Online.Available on Amazon!
Array ConstantsExcel will often let you use an array of values where you would normally use a range of cells.The Excel version:=VLOOKUP( [StatusCode], {"a","Active";"i","Inactive";"c","Closed"}, , 2, 0 )
.csharpcode, .csharpcode pre
{
font-size: 15px !important;
color: black;
font-family: consolas, "Courier New", Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-27573952590158695102018-03-26T14:28:00.001-04:002018-03-28T13:33:22.977-04:00Adding Fractions (1/4) to SharePoint Calculated Columns(The following works SharePoint 2010, 2013, 2016, SharePoint Online, and most likely, 2019!)0, 1/8, 1/4, 3/8, 1/2, 5/8, 3/4, and 7/8
This is a sample from my soon to be published book “SharePoint Calculated Columns and Validation Formulas”. If you would like to see a lot more about what you can do with Calculated Columns, and maybe win a free book, attend my session at SharePoint Cincy 2018 Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-4396939462690093892018-03-08T12:57:00.001-05:002018-03-08T12:57:07.004-05:00Adding Roman Numerals to SharePoint
In a Calculated Column
Assuming an integer in a numeric column named the "TheNumber", just add this equation to a Calculated Column:
=ROMAN(TheNumber)
You can also select alternate formats:
See: https://support.office.com/en-us/article/ROMAN-function-D6B0B99E-DE46-4704-A518-B45A0F8B56F5
In a Page, Content Editor Web Part or a Rich Text Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-66311847738649402612018-03-07T22:49:00.001-05:002018-03-07T23:08:46.221-05:00A SharePoint Calculated Column for all 50 States (workarounds for nested IF limits!)
Just in case you might ever need a formula to convert state abbreviations into state names…
You will need 50 nested IFs,
but SharePoint 2007 and 2010 only allows 7, and 2013 and later only allows 19.
You will need a little more than 1300 characters in the formula,
but SharePoint 2007 and 2010 only allow 1024. (2013 and later are around one billion!)
The trick for the IFs is to only nest 19 Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-49819614533389609242018-02-18T15:26:00.001-05:002018-02-18T15:51:06.835-05:00Numbers are Being Added to My SharePoint List Internal NamesWhen you create a list or library, the name you enter becomes both the internal name (used in the url), and the display name. When you rename a list, only the display name is changed. If you later create a new list with the same name as a renamed list’s original name, the new list’s internal name will have a number added.Here are the steps to show what's happening:Create a new Custom list and Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-6074070440029916342.post-24279179664880001542018-02-15T14:44:00.001-05:002018-02-15T14:47:35.599-05:00Run SharePoint 2013 and 2016 Search Reports from PowerShellUpdated to include IDs for SharePoint 2016! Original article here. Update! Need these reports for every site collection in the farm? See Part 2: http://techtrainingnotes.blogspot.com/2015/04/run-sharepoint-2013-search-reports-from_21.html In my Search Administration class I stress that admins should dump the search reports on a regular basis as the data is only kept in detail for 14Unknownnoreply@blogger.com2tag:blogger.com,1999:blog-6074070440029916342.post-40608219996665118742018-01-23T23:23:00.001-05:002018-01-23T23:30:38.158-05:00SharePoint 2016 Durable Links
I recently had a question in class about “Durable Links”. I did a search of the Microsoft sites to find anything official on SharePoint 2016 “Durable Links”, and basically only found a beta vintage blog article.
https://blogs.technet.microsoft.com/wbaer/2015/09/22/durable-links-in-sharepoint-server-2016-it-preview/
While I did find a number of other blog articles from the beta period, mostly Unknownnoreply@blogger.com0