The alternative ‘AutoPick’ free slot finder for Outlook Calendar

You might recognize the situation where you have to schedule meetings in Microsoft Outlook and it's impossible to find a free slot where all required participants are fully available.

Solution

To deal with this situation I wrote an Outlook Macro / Add-In that automatically finds the best slot within a pre-defined period. ‘The best slot’ is defined as the slot where most participants are at least tentatively available.

Why the ‘AutoPick’ functionality is not sufficient

Microsoft Outlook already has the feature to automatically select a slot for all required participants:

AutoPick

This is great functionality to find a slot where all participants are fully ‘free’ / ‘available’. In reality, though, people are hardly ever fully available, esp. higher up in the organization, unless you schedule a meeting 4 months in advance. For this reason, I created an alternative version that searches for a slot where required participants should at least be available ‘tentatively’. So a slot like this will be suggested:

Free slot

Installation

In Outlook, open the code editor by pressing Alt+F11, paste the code from here, go to the scheduling assistant and right-click your ribbon, and select ‘Customize the Ribbon’

Customize the Ribbon

select macros:

Select Macro

and add the Macro to a custom ‘Macro folder’ in your ribbon:

Add to custom group

You can rename your macro and display an icon as desired with the ‘rename’ option in this menu:

Rename and select icon

The slot finder will appear next to the regular one:

New slot finder in menu

The code

Outlook exposes the calendar information of participants with the FreeBusy method. This method returns the availability of the provided participant in the next 4 weeks as a character string. Each character represents a slot of a provided amount of minutes.

Logical structure

The below code will iterate through the slots and check if all participants are fully available or partially available within a chosen time frame (users are prompted to provide the number of weeks to evaluate). If no slot is found, a new search (loop iteration) will search again with adjusted success criteria, the loop with exit (and select the slot) when the number of participants minus 1 are able to at least tentatively join the meeting. The next iteration will exit with the number of participants minus 2, etc.

High-level loop structure — with line number reference

Scroll down to view the code with line numbers.

Line 42 starts a loop that counts the number of attendees that need to be removed from the success criteria. Line 47, checks if there is sufficient availability among the participants (within the time frame). During the first round of evaluations, all participants need to be Free or Tentatively available, in the next round all participants minus 1 participant need to be available.

Line 43 starts the loop to evaluate all slots (that have been retrieved in lines 22–33).

Line 45 loops through all participants, to check if the participant is available (line 47).

If all required participants are available (line 51), then a check is done if the slot is a workday and workhour (line 54), if so, the user is notified that a suitable slot has been found (line 58).

	Sub check_availability()
	Dim myOlApp As New Outlook.Application
	Set myNameSpace = myOlApp.GetNamespace("MAPI")
	Set objApp = CreateObject("Outlook.Application")
	'Set objItem = Outlook.Application.ActiveExplorer.Selection.Item(1)
	Set objItem = objApp.ActiveInspector.CurrentItem
	'Set objAttendees = Outlook.Application.ActiveExplorer.Selection.Item(1).Recipients
	Set objAttendees = objItem.Recipients
	Dim oCurrentUser As Recipient
	Dim FreeBusy(20, 2) As String
	Dim BusySlot As Long
	Dim DateBusySlot As Date
	Dim i As Long
	Const SlotLength = 30
	Dim teller As Integer
	Dim eruit As Boolean
	eruit = False
	Dim aantalre As Integer
	Dim StartDate As Date
	StartDate = Format(objItem.Start, "dd-mm-yyyy") 'DateAdd("n", SlotLength, objItem.Start)
	'Debug.Print StartDate
	Dim delta As Variant
	delta0 = (DateDiff("n", DataValue(Format(objItem.Start, "dd-mm-yyyy")), objItem.Start) / SlotLength)
	st = CDate(CLng(objItem.Start))
	delta = ((DateDiff("n", st, objItem.Start)) / SlotLength)
	aantalre = 0
	For X = 1 To objAttendees.Count
	If (objAttendees(X).Type = 1 And objAttendees(X).Sendable = True) Or objAttendees(X).Index = 1 Then
	Set myRecipient = myNameSpace.CreateRecipient(objAttendees(X).Address)
	On Error Resume Next
	FreeBusy(X, 1) = myRecipient.FreeBusy(StartDate, SlotLength, True)
	'debug.print objAttendees(X).Name & " " & vbCrLf & Left(FreeBusy(X, 1), 200)
	If Err.Number < 0 Then
	MsgBox "Unable to get Calendar for " & objAttendees(X).Name
	End If
	FreeBusy(X, 2) = objAttendees(X).Name
	aantalre = aantalre + 1
	End If
	Next
	'debug.print Len(FreeBusy(1, 1))
	Dim Message, Title, Default, aantal
	Message = "Enter number of weeks (max 4)" ' Set prompt.
	Title = "Input for max number of weeks" ' Set title.
	Default = "1" ' Set default.
	' Display message, title, and default value.
	aantal = InputBox(Message, Title, Default)
	For re = 0 To objAttendees.Count
	For i = delta + 2 To (aantal * 7 * 24 / (SlotLength / 60)) + delta + 2 'delta is the adjustment for the hours, since FreeBusy works with whole days
	teller = 0
	For Y = 1 To 20
	If Len(FreeBusy(Y, 1)) > 1 Then
	If CLng(Mid(FreeBusy(Y, 1), i, 1)) = 0 Or CLng(Mid(FreeBusy(Y, 1), i, 1)) = 1 Then
	teller = teller + 1
	End If
	If teller = aantalre - re Then
	BusySlot = (i - 1) * SlotLength
	DateBusySlot = DateAdd("n", BusySlot, StartDate)
	If TimeValue(DateBusySlot) >= TimeValue(#9:00:00 AM#) And TimeValue(DateBusySlot) <= TimeValue(#5:00:00 PM#) And Not (Weekday(DateBusySlot) = vbSaturday Or Weekday(DateBusySlot) = vbSunday) Then
	Debug.Print " first open interval:" & Y & "I: " & FreeBusy(Y, 2) & "i: " & i & _
	vbCrLf & _
	Format$(DateBusySlot, "mm\/dd\/yyyy hh:mm AM/PM")
	MsgBox "Found slot for " & aantalre - re & "/" & aantalre & " participants"
	'objItem.Start = Format$(DateBusySlot, "mm\/dd\/yyyy hh:mm AM/PM") 20221229, changed below as well
	objItem.Start = Format$(DateBusySlot, "dd\/mm\/yyyy hh:mm AM/PM")
	eruit = True
	Exit For
	End If ' close when found
	End If 'close when all recipients have been evaluated
	End If 'close when all recipients in the array have been evaluated
	If eruit = True Then
	Exit For
	End If
	Next 'Next Y, next recipient in array
	If eruit = True Then
	Exit For
	End If
	Next 'next time entry in array
	If eruit = True Then
	Exit For
	End If
	Next
	Set objApp = Nothing
	Set objItem = Nothing
	Set objAttendees = Nothing
	Set myNameSpace = Nothing
	End Sub

view raw Freeslot_finder hosted with ❤ by GitHub

A few more details

The meeting object is set in line 5, so the meeting attendees can be retrieved in line 6.

Lines 22–33 retrieve the FreeBusy information for the recipients and stores this information in an array. The FreeBusy information is provided as a character string. Each character (number), reflects the availability of this recipient:

FreeBusy States

When looping through the list of recipients, the character string that holds the availability information is added to an array:

Lines 36–41prompt the user to request the time frame within which the best slot needs to be found (1 to 4 weeks).

At line 43, the loop is initiated that loops through all slots that fit within the predefined time window. The ‘i ’variable represents a slot (of f.e. 30 minutes). ‘i’ also represents a character position in the availability string(s).

Delta is the number of slots from midnight of the day that this meeting has initially been set. This is required since the start date for FreeBusy has day-level granularity.

At line 45, the loop to evaluate the availability for all attendees is initiated (currently limited to 20 people, can be increased).

Line 45, is the core of the code because this line checks if the user is either 0 (Available) OR 1 (The user has a tentative appointment scheduled). The latter option (1) is where this Free Slot finder is different from the regular option in Outlook.

Conclusion

There is probably a lot that can be improved in this code. I can share that it has been a huge time saver for me and I hope it helps people out there as well. Free feel to provide feedback, many thanks!

 

Add Speed Metrics like ‘Average Moving Speed’ to Strava Activity descriptions Using Python

This article describes how to automatically update your Strava Activities description with multiple speed metrics using Python. The result looks like this:

Result

The Jupyter notebook can be found here. The full Python code can be found here.

Use Case

I was looking to compare the performance of my rides (using my mobile) whereby I don’t need to rely on the average speed of the ride alone since this can be heavily negatively impacted by brief stops.

Average Moving Speed

Initially, I installed the Chrome Add-In called ‘Elevate’. This great Add-In allows you to view additional metrics about your ride.

I thought it made sense to look at the 75% quartile speed as described by Elevate:

This option adds new speed/pace data to your activity panel.

For understanding these indicators, we assume that 0% to 100% are all the speeds/paces sorted ascending you obtained during an activity.

25% Quartile: This indicator represents the speed/pace you maintained at the position “25%”. This is commonly called “Lower quartile” or Q1.

50% Median: This indicator represents the speed/pace you maintained at the position “50%”. It’s simply the median…

75% Quartile: This indicator represents the speed/pace you maintained at the position “75%”. This is commonly called “Upper quartile” or Q3.

These indicators can be more meaning full than average speed/pace itself to analyse activity speed/pace. For example, you upload an activity in which you ride pretty fast for a long time. Then, you expect to have a good average speed. Unfortunately, you have to go through the city to go home where many red lights and cars behavior slow you down. Your average speed/pace then drops very quickly and do not highlight your effort of “riding fast”. In this example, the 75% quartile speed/pace is representative of the effort you made (eliminating slow speeds/paces associated with the cross of the city).

Since I was not entirely satisfied with this metric I created an alternative one, the “Average Moving Speed (20+)”.

This metric is calculated by eliminating all activity data points where you are biking less than 20 KM/H and then re-calculating the average speed. This metric describes the average speed when I am actually biking and not waiting for a traffic light f.e.

Average moving speed — Implementation

In order to calculate the “Average Moving Speed” the following steps are executed in Python:

1. Retrieve the activity data points (time and distance).
2. Calculate speed per data point:

• Per Data Point calculate the time and distance difference compared to the previous data point.
• Calculate the speed for each data point using the time & distance differences.

3. Sort the data frame on speed.

4. Select all the data points where the speeds are lower than 20 KM/H.

5. Calculate the max time and distance in 4.

6. Deduct 5 from the total activity time & distance (1).

7. Re-calculate the average speed using 6.

There is probably a more elegant way to do this, I am always open to suggestions.

The code for the above steps is given in lines 20–28 in the below code snapshot.

	def update_activity_post(idpar, distpar, timepar):
	r = get_activitydata(idpar)
	if list(r.keys())[0] == 'distance':
	df_res = create_dataframe(r)
	if max(df_res['time']) / 60 > 10:
	result_10 = calculate_split(df_res, 10)
	else:
	result_10 = ['0', '0', '0']
	if max(df_res['time']) / 60 > 20:
	result_20 = calculate_split(df_res, 20)
	else:
	result_20 = ['0', '0', '0']
	if max(df_res['time']) / 60 > 30:
	result_30 = calculate_split(df_res, 30)
	else:
	result_30 = ['0', '0', '0']
	subset = df_res[df_res['speed']<=20]
	maxtimediff = max(subset['sumcumtimediff'])
	maxdistdiff = max(subset['sumcumdistdiff'])
	movingspeed = round((((distpar - maxdistdiff) / (timepar - maxtimediff)) * 3.6),2)
	timemoving = round(((timepar - maxtimediff) / 60),0)
	prctimemoving = round((((timepar - maxtimediff) / timepar) * 100),0)
	data = {
	'description': 'Avg. Moving speed (20+): ' + str(movingspeed) + ' (' + str(prctimemoving) + '%, ' + str(timemoving) + ' mins )' + '''
	50% Qrt Speed: ''' + str(round(min(df_res[df_res['percentilerounded']==50]['speed']),2)) + '''
	Best 10 min Speed: ''' + result_10[0] + ' @ ' + result_10[1] + ' min & ' + result_10[2] + ' km.' + '''
	Best 20 min Speed: ''' + result_20[0] + ' @ ' + result_20[1] + ' min & ' + result_20[2] + ' km.' + '''
	Best 30 min Speed: ''' + result_30[0] + ' @ ' + result_30[1] + ' min & ' + result_30[2] + ' km.' + '''
	75% Qrt Speed: ''' + str(round(min(df_res[df_res['percentilerounded']==75]['speed']),2))
	}
	update_post(idpar, data)

view raw strava_movespeed hosted with ❤ by GitHub

Codeblock Update_Activity

Full implementation — code walkthrough

In order to update the description of multiple recent Strava ride activities with several metrics, a number of steps are required.

1. Get an Access token for the Strava API.
2. Retrieve recent activities.
3. Per activity:

• Store Data Points in Data Frame.
• Calculate Metrics like (10 minutes Best Speed, 75% quartile speed, Average Moving Speed)
• Update Strava Activity

The code

1. Get an Access token for Strava API

For this part, I could rely on a great post by 

Benji Knights Johnson

. My implementation of his code is given below:

	#AUTHORIZE
	#https://medium.com/swlh/using-python-to-connect-to-stravas-api-and-analyse-your-activities-dummies-guide-5f49727aac86
	#http://www.strava.com/oauth/authorize?client_id=01234&response_type=code&redirect_uri=http://localhost/exchange_token&approval_prompt=force&scope=read,activity:read_all,activity:write
	import requests
	import json
	# Make Strava auth API call with your
	# client_code, client_secret and code
	response = requests.post(
	url = 'https://www.strava.com/oauth/token',
	data = {
	'client_id': 01234,
	'client_secret': '---',
	'code': 'the code from the response',
	'grant_type': 'authorization_code'
	}
	)
	#Save json response as a variable
	strava_tokens = response.json()
	# Save tokens to file
	with open('strava_tokens.json', 'w') as outfile:
	json.dump(strava_tokens, outfile)
	# Open JSON file and print the file contents
	# to check it's worked properly
	with open('strava_tokens.json') as check:
	data = json.load(check)
	print(data)

view raw authorize hosted with ❤ by GitHub

One part was very important here, the scope, you need to ask for scope=read,activity:read_all,activity:write

Without this level of authorization, we will not be able to update the Strava Activity post.

http://www.strava.com/oauth/authorize?client_id=13077&response_type=code&redirect_uri=http://localhost/exchange_token&approval_prompt=force&scope=read,activity:read_all,activity:write

The above Python code execution is a one-time effort, from here we can refresh the token.

The below function will refresh the token:

	#refresh token
	def refresh_token():
	import requests
	import json
	import time
	# Get the tokens from file to connect to Strava
	with open('strava_tokens.json') as json_file:
	strava_tokens = json.load(json_file)
	# If access_token has expired then
	# use the refresh_token to get the new access_token
	if strava_tokens['expires_at'] < time.time():
	# Make Strava auth API call with current refresh token
	print('token refreshed')
	response = requests.post(
	url = 'https://www.strava.com/oauth/token',
	data = {
	'client_id': 01234,
	'client_secret': '----',
	'grant_type': 'refresh_token',
	'refresh_token': strava_tokens['refresh_token']
	}
	)
	# Save response as json in new variable
	new_strava_tokens = response.json()
	# Save new tokens to file
	with open('strava_tokens.json', 'w') as outfile:
	json.dump(new_strava_tokens, outfile)
	# Use new Strava tokens from now
	strava_tokens = new_strava_tokens
	# Open the new JSON file and print the file contents
	# to check it's worked properly
	with open('strava_tokens.json') as check:
	data = json.load(check)
	print(data)

view raw refresh_token hosted with ❤ by GitHub

2. Retrieve Recent Activities

The below code is the main code that loops through recent activities to calculate several metrics and finally update the Strava Activity.

	#GET ACTIVITIES
	import requests
	import pandas as pd
	from pandas.io.json import json_normalize
	import json
	import csv
	refresh_token()
	# Get the tokens from file to connect to Strava
	with open('strava_tokens.json') as json_file:
	strava_tokens = json.load(json_file)
	# Loop through all activities
	url = "https://www.strava.com/api/v3/activities"
	access_token = strava_tokens['access_token']
	# Get first page of activities from Strava with all fields
	r = requests.get(url + '?access_token=' + access_token)
	r = r.json()
	df = pd.json_normalize(r)
	#df.to_csv('strava_activities_all_fields.csv')
	df.head(n=20)
	for index, acrow in df.head(n=2).iterrows():
	print(acrow['id'])
	if acrow['distance'] > 2000 and acrow['type'] == 'Ride':
	update_activity_post(acrow['id'], acrow['distance'], acrow['elapsed_time'] )

view raw get_activities hosted with ❤ by GitHub

Get Activities Code

• The token is refreshed in line 8.
• The activities are retrieved and stored in a data frame in lines 13–19.
• For each activity, metrics are calculated and published to Strava on lines 23 to 26.

3. Per activity

The main function (called in line 26 in ‘Get Activity Code’, see above) to calculate the metrics and update each Strava Activity is given below:

	def update_activity_post(idpar, distpar, timepar):
	r = get_activitydata(idpar)
	if list(r.keys())[0] == 'distance':
	df_res = create_dataframe(r)
	if max(df_res['time']) / 60 > 10:
	result_10 = calculate_split(df_res, 10)
	else:
	result_10 = ['0', '0', '0']
	if max(df_res['time']) / 60 > 20:
	result_20 = calculate_split(df_res, 20)
	else:
	result_20 = ['0', '0', '0']
	if max(df_res['time']) / 60 > 30:
	result_30 = calculate_split(df_res, 30)
	else:
	result_30 = ['0', '0', '0']
	subset = df_res[df_res['speed']<=20]
	maxtimediff = max(subset['sumcumtimediff'])
	maxdistdiff = max(subset['sumcumdistdiff'])
	movingspeed = round((((distpar - maxdistdiff) / (timepar - maxtimediff)) * 3.6),2)
	timemoving = round(((timepar - maxtimediff) / 60),0)
	prctimemoving = round((((timepar - maxtimediff) / timepar) * 100),0)
	data = {
	'description': 'Avg. Moving speed (20+): ' + str(movingspeed) + ' (' + str(prctimemoving) + '%, ' + str(timemoving) + ' mins )' + '''
	50% Qrt Speed: ''' + str(round(min(df_res[df_res['percentilerounded']==50]['speed']),2)) + '''
	Best 10 min Speed: ''' + result_10[0] + ' @ ' + result_10[1] + ' min & ' + result_10[2] + ' km.' + '''
	Best 20 min Speed: ''' + result_20[0] + ' @ ' + result_20[1] + ' min & ' + result_20[2] + ' km.' + '''
	Best 30 min Speed: ''' + result_30[0] + ' @ ' + result_30[1] + ' min & ' + result_30[2] + ' km.' + '''
	75% Qrt Speed: ''' + str(round(min(df_res[df_res['percentilerounded']==75]['speed']),2))
	}
	update_post(idpar, data)

view raw strava_movespeed hosted with ❤ by GitHub

Codeblock Update_Activity

This function uses a number of supporting functions that are described below:

• get_activitydata (retrieves the activity data points).
• create_dataframe (creates a data frame with additional calculated values that support calculating the metrics)
• calculate_split (returns the best speed held for the provided amount of time)
• update_post (updates the description of the Strava Activity Post)

Supporting functions

get_activitydata performs a basic API call to retrieve recent activities.

	def get_activitydata(idpar):
	url = "https://www.strava.com/api/v3/activities/" + str(idpar) + "/streams/time"
	r = requests.get(url + '?access_token=' + access_token + '&types=["time"]&key_by_type=true')
	r = r.json()
	return r

view raw getdata hosted with ❤ by GitHub

create_dataframe takes the JSON activity data and returns a pandas data frame with additional supporting columns to support the calculation of multiple speed metrics.

	def create_dataframe(r):
	df = pd.DataFrame(r['distance']['data'])
	df2 = pd.DataFrame(r['time']['data'])
	df3= pd.concat([df, df2], axis=1)
	df_res = pd.concat([df3.shift(1), df3], axis=1)
	df_res.columns = ['dist-1', 'time-1', 'dist', 'time']
	df_res['time_diff'] = df_res['time'] - df_res['time-1']
	df_res['dist_diff'] = df_res['dist'] - df_res['dist-1']
	df_res['speed'] = (df_res['dist_diff'] / df_res['time_diff']) * 3.6
	df_res['sumcumtimediff'] = df_res.sort_values(by=['speed'], ascending=True)['time_diff'].cumsum()
	df_res['sumcumdistdiff'] = df_res.sort_values(by=['speed'], ascending=True)['dist_diff'].cumsum()
	time = df_res.loc[df_res['speed'].size-1]['time']
	df_res['percentile'] = (df_res['sumcumtimediff']) / time
	df_res['percentilerounded'] = round((df_res['percentile'] * 100) , 0)
	return df_res

view raw create_dateframe hosted with ❤ by GitHub

Lines 2–3 retrieve the distance and elapsed time lists.
Line 4 creates a data frame.
Line 5 joins the data frame with itself and joins the rows by index, whereby its joining on the previous row so the time and distance difference between the current and previous row can be calculated.
Lines 7 & 8 calculate the differences.
Line 9 calculates the speed based on the differences.
Lines 10 & 11 calculate the cumulative for speed and time.
Line 12 retrieves the total time duration of the activity.
Line 13 can then calculate the speed percentile, this is the percentile position of the sorted speed. This supports the 25%, 50%, and 75% percentile speed positions as defined by Elevate.

calculate_split returns the best speed that was held for the provided time period. Lines 8 & 9 find the row that is closest to the start of the provided time frame. This was required because each row does not represent a fixed time difference from the next row.

Lines 23 and 24 retrieve the time and distance when the best split started.

	def calculate_split(df_res, minutes):
	lst = []
	#minutes = 20
	interval = minutes * 60
	for index, row in df_res.iterrows():
	if df_res[df_res['time']<=row['time']-interval]['time'].size != 0:
	val = max(df_res[df_res['time']<=row['time']-interval]['time'])
	record = df_res[df_res['time']==val]
	timediff = row['time'] - record['time']
	distdiff = row['dist'] - record['dist']
	speed = (distdiff / timediff) * 3.6
	#print(str(row['time']) + " avg speed " + str(speed.to_string(index=False)))
	lst.append(float(speed.to_string(index=False)))
	else:
	lst.append(0)
	val = max(lst)
	p = lst.index(val)
	df2 = df_res.filter(items = [p], axis=0)
	timeval = max(df_res[df_res['time']<=int(float((df2['time']-(60*minutes)).to_string(index=False)))]['time'])
	kmrecord = df_res[df_res['time']==timeval]
	#print(int(float((df2['time']-(60*minutes)).to_string(index=False))))
	return [str(round(val,2)), str(int(float(((df2['time']/60)-minutes).to_string(index=False)))),
	str(round(float(((kmrecord['dist']/1000)).to_string(index=False)),2))]

view raw create_split hosted with ❤ by GitHub

A quick comparison with Elevate shows identical results:

Evevate, best splits table

Metrics added by Python script

update_post updates the description of the Strava Activity with the metrics that have been calculated.

	def update_post(idpar, datapar):
	endpoint = "https://www.strava.com/api/v3/activities/" + str(idpar)
	data = datapar
	headers = {"Authorization": "Bearer " + access_token}
	requests.put(endpoint, data=data, headers=headers).json()
	print('updated')

view raw update_post hosted with ❤ by GitHub

Conclusion

I hope this article is of value to you. I wrote it to learn from you how to improve and to help people get a head start with creating something similar.

The jupyter notebook can be found here. The full python code can be found here.

This article has been posted on medium as well, please find it here. 

Introduction

In this post I will share a piece of Power M code that automatically detects the column type. 

The below Power M function will take a table as a parameter and return a table where the data types have been set.

Please mind, it currently only works for the datatypes; datetime, date, number and text. This should not be an issue for those who are skilled in M, since you can easily extend the function. 

Pls also mind the final row in the code, here you see that fn is called upon whereby a table is passed, but also the number of rows and the allowed error margin. 

Credits to Cris Webb for explaining how Expression. Evaluate works. Credits to Imke for explaining how the transformcolumn types work. 

  
(tbl as table) as table  =>
let
    fn = (tbl as table, col as text, numberofrecords as number, marginforerror as number) as type =>
    let
        LijstmetValues = List.FirstN( Table.Column(tbl, col),numberofrecords), 
        Env = Record.Combine({[L=LijstmetValues],[DTF = #shared[DateTime.From]], [DF = #shared[Date.From]], [NF = #shared[Number.From]], [TF = #shared[Text.From]], [LT = #shared[List.Transform]], [LS = #shared[List.Select]], [LC = #shared[List.Count]]   }),
        NumberOfErrors = List.Transform({"DTF", "DF", "NF", "TF"}, each Expression.Evaluate("
                    LC(LS(
                        LT(L, each try  " & _ & "(_) otherwise ""Error""), each _ = ""Error""))", Env)),
        CheckWithinMargin = List.Transform(NumberOfErrors, each _ <= numberofrecords * marginforerror),
        typenr = List.PositionOf(CheckWithinMargin, true),  

        FirstTypeWithinMargin =  {"datetime", "date", "number", "text"}{typenr},
        CheckType = if List.Distinct(LijstmetValues){0} = null or FirstTypeWithinMargin = -1 then 4  else FirstTypeWithinMargin,
        result = Record.Field([number = type number,date = type date,datetime = type datetime,text = type text, any = type any],CheckType)
    in result,
    Columnsto2Type = Table.TransformColumnTypes(tbl, List.Transform(Table.ColumnNames(tbl), each {_,  fn(tbl,_, 7, 0.1)}))    
in
    Columnsto2Type

The animation below hopefully clarifies how you could potentially use this code. Notice that I removed the steps that automatically detected the datatypes (when creating the table initially), so the function can take on this task when we are manipulating multiple tables. This should demonstrate some more advanced use cases where we need to detect data types. 

 

Kind regards, Steve.