ncalm

#"Converted currency text" = Table.AddColumn(#"Previous query step","new_column_name",each 
let 
  //convert the original text to lower case
  lower = Text.Lower([currency_as_text]),,
  
  //add as many Text.Replace as you need to remove unwanted words
  //in case of many words to remove, could iterate a list of words
  words_removed = Text.Replace(lower,"unknown",""),
  
  //for text $180B, following split creates a list {"$180","b"}

ncalm

/*
CLEANCURRENCYTEXT

This lambda function will quickly convert currency values stored as text 
with a scaling suffix such as "B" (for billions) or "M" (for millions) 
and so on, to a number all in the same scale

inputs:
  - val, a single value to be converted as described above
  - [mapping], a two-column array or range of suffix:power pairs such as 

ncalm

/*

LIST.COMPARE

Compares the items in two lists and shows where an item exists in one list and not the other

Inputs:
  Required:
  - list1 - a one-dimensional range or array, either vertical or horizontal
  - list2 - a one-dimensional range or array, either vertical or horizontal

ncalm

/*
Intent here is to transform many columns at once without hard-coding the column names into the query (in case the column names change in an update to the source)

Longer with comments to explain (shorter embedded version below)"
*/


let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

ncalm

/*
pd.qcut

First in a series of Excel Lambda implementations of Python Pandas methods.

pd.qcut will create groups, or bins, for a continuous numerical variable. 



Inputs

ncalm

/*
pd.rolling.aggregate

Calculates a rolling aggregate of a variable where each window is of size window and the function agg is applied to each window

Inputs:
  x: a single-column numerical variable, sorted in the order the user expects to calculate rolling calculations on
  window: an integer specifying the window length/width. For example, if window is 3, then the aggregate will be applied over the set of 3 rows ending in the current row
  agg: a text string specifying which aggregate function should be applied over each window
  

ncalm

/*
INFLECTIONPOINTS

Identifies the points y_x in an ordered numerical variable where the adjacent points y_x-1 and y_x+1 are either both higher or both lower than y_x

Inputs:
  y: an ordered numerical array
  
Returns:
  An array the same size as y containing -1 where y_x is lower than the adjacent points, 1 where y_x is higher than the adjacent points, otherwise 0.

ncalm

/*
SQL.TYPE

Returns an array of values from the set {"text","date","number"} indicating how a cell's value should be formatted for use in a SQL statement

Inputs:
	- in_list: a 1-dimensional array or range, which can be horizontal or vertical

Return:
  an array the same size as in_list, containing:

ncalm

/*
RANK.DENSE

Ranks a one-dimensional array using the provided sort order. If adjacent items have the same value, they are all given the rank of the first of them. 
Ranks following equally ranked groups increment by one from the most recent rank. This is as opposed to RANK.EQ where following ranks are given the rank
they would have received if the same-ranked items were given different ranks. As such, this function behaves similarly to SQL's DENSE_RANK

Inputs:
  Number - A number or array of numbers to find the rank for from the ranks given by Ref sorted by Order
  Ref - A list of numbers to be ranked, from which the rank of Number will be found

ncalm

/*
RANK.AVERAGE

Implementation of RANK.AVG that can be used in MAKEARRAY, SCAN etc. 

Inputs and output is the same as RANK.AVG with the exception that if Number is not found in Ref, the cell will output "No rank" instead of an error value.

*/

RANK.AVERAGE =LAMBDA(Number,Ref,[Order],