leader's blog

I wanted my script to look at values but ignore the "Null" values for the comparison 

Table.AddColumn(#"Split column by delimiter 1", "PSStudentAddressMatches", each if [PSMailingAddress.1] <> [PSMailingAddress.2] and [PSMailingAddress.2] <> null  then "PS Address Flag"

else if [PSMailingAddress.1] <> [PSMailingAddress.3] and [PSMailingAddress.3] <> null  then "PS Address Flag"

else if [PSMailingAddress.1] <> [PSMailingAddress.4] and [PSMailingAddress.4] <> null then "PS Address Flag"

Table.AddColumn(#"Attendance Hold", "AllHHIDHold", each if Text.Contains([HHIDHomeless], "Homeless") then "OK" else if Text.Contains([ComputerHoldonHHID], "Hold") or  Text.Contains([HotSpotHoldonHHID], "Hold") or  Text.Contains([AddressHold], "Hold") or  Text.Contains([AttendanceHoldonHHID], "Hold") then "Hold" else "OK")

if [StudentsinHHID] = 1 and [Un_Excused_Abs_HHID] > 59 then "AttendanceHold" else if [StudentsinHHID] = 2 and [Un_Excused_Abs_HHID] > 119 then "AttendanceHold" else if [StudentsinHHID] = 3 and [Un_Excused_Abs_HHID] > 179 then "AttendanceHold" else if [StudentsinHHID] = 4 and [Un_Excused_Abs_HHID] > 239 then "AttendanceHold" else if [StudentsinHHID] = 5 and [Un_Excused_Abs_HHID] > 299 then "AttendanceHold" else if [StudentsinHHID] = 6 and [Un_Excused_Abs_HHID] > 359 then "AttendanceHold" else if [StudentsinHHID] = 7 and [Un_Excused_Abs_HHID] > 419 then "AttendanceHold" e

Table.Group(#"Expanded OnbaseData", {"Family_Ident"},

{{"HHIDStudentTotal", each List.Count(List.Distinct([Student_Number])), Int64.Type},

{"ITSRecipient", each List.First([Technology Subsidy Recipient]), type nullable text},

{"StudentsNames", each Text.Combine([LastFirst]," - "), type nullable text},

{"StudentsIDs", each Text.Combine([Student_Number]," - "), type nullable text},

{"ComputerHolds", each Text.Combine(([Unreturned Computer]," - "), type nullable logical},

Don't want to forget how to do this. :)

Body Code:
{'__metadata':
{'type': 'SP.Data.GraduationExportsItem'},
'Title':'Sample',
'FileLeafRef':'PSEnterpriseExport.csv'}

I thought there wasn't enough documentation on the web for this.

This is how I connected to MS Bookings
Auth type: "Log in with Microsoft Entra ID"

Base Resource URL: https://graph.microsoft.com

Microsoft Entra ID Resource URI (Application ID URI): https://graph.microsoft.com

Then it will ask you for your account info when you click "Sign in"

Save you some typing... use find and replace with your column name.

Table.ReplaceValue(#"Col 1", each [YOURCOLUMN],

each if [YOURCOLUMN] ="STREET" then "ST"

else if [YOURCOLUMN] ="ROAD" then "RD"

else if [YOURCOLUMN] ="DRIVE" then "DR"

else if [YOURCOLUMN] ="COURT" then "CT"

else if [YOURCOLUMN] ="LANE" then "LN"

else if [YOURCOLUMN] ="BOULEVARD" then "BLVD"

else if [YOURCOLUMN] ="EXPRESSWAY" then "EXPY"

else if [YOURCOLUMN] ="EXTENSION" then "EXT"

else if [YOURCOLUMN] ="FREEWAY" then "FWY"

Use this is you are concating multi columns and need to remove unwanted spaces from the concat column

IE: I was concating address fields like so 
[ITSP Street Address] & " " & [ITSP Apartment Number] & " " & [ITSP City] & " " & [ITSP State] & " " & [ITSP Zip Code]

Sometimes the [ITSP Apartment Number] column did not have a value so it added an extra space for the column.

Here's how you get rid of the extra spaces with a Trim.

<ViewFields>
<FieldRef Name="LinkFilename"/>
<FieldRef Name="FileLeafRef"/>
<FieldRef Name="LinkFilenameNoMenu"/>
</ViewFields>

This Power Automate will trigger on when a new Team Form in SharePoint is submitted

• It will create a new item in a SharePoint list
•  It will check a SharePoint document library is a folder exists, if it does not exist, the flow will create a new folder and place uploaded file in the folder.
•  If the folder does exist, it will place the uploaded file into the folder.

1. When a new response is submitted