Forum Discussion
Slicer & CalcilatedColumn
Hi, i have a report with a Slicer "As of Date" the is linked to the DateDimension, all works great, i need to add a Histogram for Age of the Customer Facts, as such i added a calculated co...
Hi ronenbitman,
When I run your Power Query statement, it return error message. Why do you use so multiple commas which lead to error between two value? Could you please share more details.
Best Regards,
Angelia
Hi Angelia,
sorry for that, i didn't put the 'null' string & the date,
hope this is better
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,"Equity",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,"Equity",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"Hi ronenbitman，
I create the sample data table using Power Query. I think it is 'Investor Facts' table, in your DAX above 'Investor Facts'[Investment Type]="Provident Fund", I can't find "Provident Fund" value in the new created table. And there is no column name, I am unbale to connect it with your given DAX expression. Could you please share more details for further analysis? Thanks a lot.
Best Regards,
AngeliaHi 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