Slicer & CalcilatedColumn

Hi Angelia,

indeed this is the Investor Facts table,

the snapshot of the sample data that i used didn't have 'Provident Fund' value within the [Investment Type] column, however the column does exist (i added spaces to show where) and i changed the data a bit to include this value,

 let
    src=Table.FromRows({
{1,false,null,"A","Private",null,null,null,null,null,"",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("01/11/2016 12:04"),DateTime.FromText("01/11/2016 12:04"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Private",253.1223275,"Private",Date.FromText("31/07/2016"),8},
{2,false,null,"C","Private",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"y",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("22/11/2016 11:28"),DateTime.FromText("22/11/2016 11:28"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Private",52.01478875,"Private",Date.FromText("31/10/2016"),11},
{3,false,null,"D","Company",null,null,null,null,null,"Guernsey",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("11/12/2016 16:45"),DateTime.FromText("11/12/2016 16:45"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Company",4068.535781,"Company",Date.FromText("31/01/2016"),2},
{4,false,null,"E","Private",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("01/11/2016 12:04"),DateTime.FromText("01/11/2016 12:04"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Private",161.4756306,"Private",Date.FromText("31/05/2016"),6},
{5,false,null,"F","Private",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("01/11/2016 12:04"),DateTime.FromText("01/11/2016 12:04"),null,null,null,null,"Active",null,null,"Provident Fund",null,"","",null,"Other","Family Office",260.844415,"Family Office",Date.FromText("31/03/2016"),4},
{6,false,null,"G","Company",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"y",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("11/12/2016 16:58"),DateTime.FromText("11/12/2016 16:58"),null,null,null,null,"Active",null,null,"Provident Fund",null,"","",null,"Other","Investment House",262.6206747,"",Date.FromText("31/08/2016"),9},
{7,false,null,"H","Private",null,null,null,null,null,"",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"y",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("01/11/2016 12:05"),DateTime.FromText("01/11/2016 12:05"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Private",97.16964962,"Private",Date.FromText("31/08/2016"),9},
{8,false,null,"I","Company",null,null,null,null,null,"Switzerland",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"y",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("01/11/2016 12:05"),DateTime.FromText("01/11/2016 12:05"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Company",506.244655,"Company",Date.FromText("31/07/2016"),8},
{9,false,null,"J","Private",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"y",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("22/11/2016 11:28"),DateTime.FromText("22/11/2016 11:28"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Private",260.0739437,"Private",Date.FromText("31/10/2016"),11},
{10,false,null,"K","Private",null,null,null,null,null,"",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("30/11/2016 13:43"),DateTime.FromText("30/11/2016 13:43"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Family Office",525.2413493,"Family Office",Date.FromText("31/08/2016"),9},
{11,false,null,"L","Company",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("02/01/2017 02:36"),null,DateTime.FromText("01/11/2016 12:04"),DateTime.FromText("01/11/2016 12:04"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Company",3155.20719,"Company",Date.FromText("31/01/2016"),2},
{12,false,null,"DS","Private",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"AG",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("01/11/2016 12:05"),DateTime.FromText("01/11/2016 12:05"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Private",156.1277687,"Private",Date.FromText("31/03/2013"),0},
{13,false,null,"M","Private",null,null,null,null,null,"UK",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("01/11/2016 12:04"),DateTime.FromText("01/11/2016 12:04"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Private",1093.030333,"Private",Date.FromText("29/02/2016"),3},
{14,false,null,"FF","Company",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"AG",DateTime.FromText("02/01/2017 02:36"),null,DateTime.FromText("01/11/2016 12:05"),DateTime.FromText("01/11/2016 12:05"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Company",2394.249123,"Company",Date.FromText("31/03/2013"),0},
{15,false,null,"N","Private",null,null,null,null,null,"",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("28/11/2016 09:15"),DateTime.FromText("28/11/2016 09:15"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Private",782.6398577,"Private",Date.FromText("30/04/2016"),5},
{16,false,null,"AA","Company",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"y",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("11/12/2016 16:44"),DateTime.FromText("11/12/2016 16:44"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Company",5426.93198,"Company",Date.FromText("31/07/2016"),8},
{17,false,null,"AB","Company",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("01/11/2016 12:04"),DateTime.FromText("01/11/2016 12:04"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Company",806.5134905,"Company",Date.FromText("30/04/2016"),5},
{18,false,null,"AC","Company",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("04/01/2017 16:09"),null,DateTime.FromText("01/11/2016 12:04"),DateTime.FromText("01/11/2016 12:04"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Company",5468.522038,"Company",Date.FromText("31/08/2016"),9},
{19,false,null,"AD","Private",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"y",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("22/11/2016 11:27"),DateTime.FromText("22/11/2016 11:27"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Private",260.0739437,"Private",Date.FromText("31/10/2016"),11},
{20,false,null,"AE","Private",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("20/12/2016 07:56"),DateTime.FromText("20/12/2016 07:56"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Private",717.2395009,"Private",Date.FromText("31/01/2016"),2},
{21,false,null,"AF","Private",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"y",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("01/11/2016 12:04"),DateTime.FromText("01/11/2016 12:04"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Private",525.2413493,"Private",Date.FromText("31/08/2016"),9},
{22,false,null,"AG","Private/",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("01/11/2016 12:04"),DateTime.FromText("01/11/2016 12:04"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Family Office",518.01409,"Family Office",Date.FromText("30/04/2016"),5},
{23,false,null,"AH","Company",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("11/12/2016 16:37"),DateTime.FromText("11/12/2016 16:37"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Family Office",525.2413493,"Family Office",Date.FromText("31/08/2016"),9},
{24,false,null,"AI","Private",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("01/11/2016 12:04"),"g",DateTime.FromText("23/11/2016 16:52"),null,DateTime.FromText("22/11/2016 13:13"),DateTime.FromText("22/11/2016 13:13"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Private",0,"Private",Date.FromText("31/05/2016"),6},
{25,false,null,"BB","Private",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("11/12/2016 16:50"),DateTime.FromText("11/12/2016 16:50"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Family Office",518.0244503,"Family Office",Date.FromText("30/04/2016"),5},
{26,false,null,"BC","Private",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("01/11/2016 12:04"),DateTime.FromText("01/11/2016 12:04"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Private",1518.733965,"Private",Date.FromText("31/07/2016"),8},
{27,false,null,"BD","Company",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("01/11/2016 12:04"),DateTime.FromText("01/11/2016 12:04"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Other","Company",207.205636,"Company",Date.FromText("30/04/2016"),5},
{28,false,null,"DD","Private",null,null,null,null,null,"",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("24/11/2016 07:43"),"A",DateTime.FromText("20/12/2016 07:55"),"g",DateTime.FromText("21/12/2016 16:10"),null,DateTime.FromText("20/12/2016 08:00"),DateTime.FromText("20/12/2016 08:00"),null,null,null,null,"Active",null,null,"Equity",null,"yes","C",null,"Israel","Private",260.3472779,"Private",Date.FromText("30/11/2016"),12},
{29,false,null,"EE","Private",null,null,null,null,null,"Israel",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"AQ",DateTime.FromText("01/11/2016 11:07"),"A",DateTime.FromText("27/12/2016 07:13"),"g",DateTime.FromText("27/12/2016 07:13"),null,DateTime.FromText("01/11/2016 12:04"),DateTime.FromText("01/11/2016 12:04"),null,null,null,null,"Active",null,null,"Equity",null,"","",null,"Israel","Family Office",260.844415,"Family Office",Date.FromText("31/03/2016"),4}
}),
    Rnm=Table.RenameColumns(Src, {{"Column1", "Id"},{"Column2", "IsDeleted"},{"Column3", "MasterRecordId"},{"Column4", "Investor Name"},{"Column5", "Investor Type"},{"Column6", "ParentId"},{"Column7", "Investor Address"},{"Column8", "Investor City"},{"Column9", "Investor State"},{"Column10", "Investor Postal Code"},{"Column11", "Investor Country"},{"Column12", "Investor Latitude"},{"Column13", "Investor Longitude"},{"Column14", "ShippingStreet"},{"Column15", "ShippingCity"},{"Column16", "ShippingState"},{"Column17", "ShippingPostalCode"},{"Column18", "ShippingCountry"},{"Column19", "ShippingLatitude"},{"Column20", "ShippingLongitude"},{"Column21", "Investor Phone"},{"Column22", "Investor Website"},{"Column23", "Industry"},{"Column24", "NumberOfEmployees"},{"Column25", "Description"},{"Column26", "MarketerID"},{"Column27", "CreatedDate"},{"Column28", "CreatedById"},{"Column29", "LastModifiedDate"},{"Column30", "LastModifiedById"},{"Column31", "SystemModstamp"},{"Column32", "LastActivityDate"},{"Column33", "LastViewedDate"},{"Column34", "LastReferencedDate"},{"Column35", "Jigsaw"},{"Column36", "JigsawCompanyID"},{"Column37", "AccountSource"},{"Column38", "SicDesc"},{"Column39", "Investor Status"},{"Column40", "Investor estimated AUM"},{"Column41", "Investor Parent Account"},{"Column42", "Investment Type"},{"Column43", "MktID"},{"Column44", "Investor Accreditied Has Decleration"},{"Column45", "Investor Accreditied Class"},{"Column46", "Investor Pitch Date"},{"Column47", "Investor Residency"},{"Column48", "Investor Parent Type"},{"Column49", "Investor Current USD Market Value "},{"Column50", "Investor Type Buckets"},{"Column51", "Latest Subscription Activity"},{"Column52", "Accredited Month Age"}}),
    #"Changed Type" = Table.TransformColumnTypes(Rnm,{{"Id", type text}, {"MasterRecordId", type text}, {"Investor Name", type text}, {"Investor Type", type text}, {"ParentId", type text}, {"Investor Address", type text}, {"Investor City", type text}, {"Investor State", type text}, {"Investor Postal Code", type text}, {"Investor Country", type text}, {"ShippingStreet", type text}, {"ShippingCity", type text}, {"ShippingState", type text}, {"ShippingPostalCode", type text}, {"ShippingCountry", type text}, {"Investor Phone", type text}, {"Investor Website", type text}, {"Industry", type text}, {"Description", type text}, {"MarketerID", type text}, {"CreatedById", type text}, {"LastModifiedById", type text}, {"Jigsaw", type text}, {"JigsawCompanyID", type text}, {"AccountSource", type text}, {"SicDesc", type text}, {"Investor Status", type text}, {"Investor Parent Account", type text}

, {"Investment Type", type text}

, {"MktID", type text}, {"Investor Accreditied Has Decleration", type text}, {"Investor Accreditied Class", type text}, {"Investor Residency", type text}, {"Investor Parent Type", type text}, {"Investor Type Buckets", type text}, {"Accredited Month Age", Int64.Type}, {"Latest Subscription Activity", type date}, {"Investor Pitch Date", type date}, {"Investor estimated AUM", type number}, {"LastReferencedDate", type datetime}, {"LastViewedDate", type datetime}, {"SystemModstamp", type datetime}, {"LastModifiedDate", type datetime}, {"CreatedDate", type datetime}, {"LastActivityDate", type date}, {"NumberOfEmployees", Int64.Type}, {"ShippingLatitude", type number}, {"ShippingLongitude", type number}, {"Investor Longitude", type number}, {"Investor Latitude", type number}, {"IsDeleted", type logical}})
in
    #"Changed Type"

kind regards,

Ronen