Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ronenbitman
Frequent Visitor

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 column in the Customer Facts table that would show the customerAge as of ReportDate,



DATEDIFF(DateOfBirth,  'Date Dimension'[Date])


now i would assumed that if i would change the slicer to a different date the histogram would change, but no it remains static, but all the rest of the report changed according to the selected date correctly

i do not have a relationship between the dateDimension & the customer (do i need one? wouldn't it filter the customer rows by the selected date?)


where am i wrong?

8 REPLIES 8
Baskar
Resident Rockstar
Resident Rockstar

For my understanding do u want get the age based on slicer selction date ?

 

If yes your aproach is wrong . the reason is if u create calculated column it is static it won't change while slicer change.

 

u have to create calculated Measure instead of Calculated Column.

 

Share ur column and sample data i will help u.

 

The idea is create new measure :

Get DOB in Var , like Var DOB = Max(DateOfBirth)

Get Max Date on Slicer   like Var Max_Date = Max('Date Dimension'[Date]).

 

then final Datediff : Return Datediff(DOB,Max_Date,DAY).

 

Note: 

If ur DOB is greater then slicer it will give u the error.

 

So add one condition in final return : 

 

Return if ( Max_Date < DOB, Blank(), Datediff(DOB,Max_Date,DAY))

 

Let me know , any help

Hi Baskar,

 

thanks for the answer

 

my initial question was not the actual one as i tried to make the explanation of my schema easier, but i understand the idea behind what you are saying, however i still unable to see how to accomplish this,
i am not able to attach the data but i attached the Schema an (hopefully) added the information that is evolved in my requirement.

basicly i have two requirements

- show the count of not accredited Investor, so i added the measure

Not Accredited Investor Count = COUNTX(Filter('Investor Facts','Investor Facts'[Accredited Month Age]<>0),'Investor Facts'[Accredited Month Age] )

Facts'[Accredited Month Age] )]

- show a histogram of Accredited Month Age - so i added a calculated column

 

Accredited Month Age = 
	VAR InternalSelectedDate = 'Date Dimension'[SelectedDate]
	VAR In12Month = EOMONTH([Latest Subscription Activity],12)
    VAR EndOfAccreditedPeriod = 
        IF(
            DAY([Latest Subscription Activity])>DAY(In12Month),
                    DATE(year(In12Month),month(In12Month),day(In12Month)),
                    DATE(year(In12Month),month(In12Month),day([Latest Subscription Activity]))
        )
    RETURN
        if(
            'Investor Facts'[Investor Accreditied Class]="A" || 'Investor Facts'[Investor Accreditied Class]="B" || 'Investor Facts'[Investment Type]="Provident Fund"
            , 0
            , if(EndOfAccreditedPeriod > NOW(), DATEDIFF(InternalSelectedDate,EndOfAccreditedPeriod,MONTH), 0)
        )

 

i added a workaround as the DateDiff is unable to add dates that are not defined in the 'Date Dimension' Table

 

could you please try to point me to the right way to implement this

 

 

 

PowerBI_SchemaRelationshop.jpg

 

many thanks for the help

 

 

 

Hi @ronenbitman,

>>i added a workaround as the DateDiff is unable to add dates that are not defined in the 'Date Dimension' Table

 

Based on my understanding, using the DateDiff function in "Accredited Month Age" measure returns an error or something else? Could you please post 'Investor Facts' table for further analysis? If your data is private, you can create sample data in similar format.

 

Best Regards,
Angelia

Hi,

 

no there is no error when i use the DateDiff, it just return empty value which eventually prevents me from receiving the requested outcome.
 attached a PQ to create the table

Table.FromRows({
{1,FALSE,,"A","Private",,,,,,"",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Private",253.1223275,"Private",31/07/2016,8},
{2,FALSE,,"C","Private",,,,,,"Israel",,,,,,,,,,,,,,,"y",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,22/11/2016 11:28,22/11/2016 11:28,,,,,"Active",,,"Equity",,"","",,"Other","Private",52.01478875,"Private",31/10/2016,11},
{3,FALSE,,"D","Company",,,,,,"Guernsey",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,11/12/2016 16:45,11/12/2016 16:45,,,,,"Active",,,"Equity",,"","",,"Other","Company",4068.535781,"Company",31/01/2016,2},
{4,FALSE,,"E","Private",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Private",161.4756306,"Private",31/05/2016,6},
{5,FALSE,,"F","Private",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Family Office",260.844415,"Family Office",31/03/2016,4},
{6,FALSE,,"G","Company",,,,,,"Israel",,,,,,,,,,,,,,,"y",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,11/12/2016 16:58,11/12/2016 16:58,,,,,"Active",,,"Equity",,"","",,"Other","Investment House",262.6206747,"",31/08/2016,9},
{7,FALSE,,"H","Private",,,,,,"",,,,,,,,,,,,,,,"y",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:05,01/11/2016 12:05,,,,,"Active",,,"Equity",,"","",,"Other","Private",97.16964962,"Private",31/08/2016,9},
{8,FALSE,,"I","Company",,,,,,"Switzerland",,,,,,,,,,,,,,,"y",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:05,01/11/2016 12:05,,,,,"Active",,,"Equity",,"","",,"Other","Company",506.244655,"Company",31/07/2016,8},
{9,FALSE,,"J","Private",,,,,,"Israel",,,,,,,,,,,,,,,"y",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,22/11/2016 11:28,22/11/2016 11:28,,,,,"Active",,,"Equity",,"","",,"Other","Private",260.0739437,"Private",31/10/2016,11},
{10,FALSE,,"K","Private",,,,,,"",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,30/11/2016 13:43,30/11/2016 13:43,,,,,"Active",,,"Equity",,"","",,"Other","Family Office",525.2413493,"Family Office",31/08/2016,9},
{11,FALSE,,"L","Company",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",02/01/2017 02:36,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Company",3155.20719,"Company",31/01/2016,2},
{12,FALSE,,"DS","Private",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"AG",27/12/2016 07:13,,01/11/2016 12:05,01/11/2016 12:05,,,,,"Active",,,"Equity",,"","",,"Other","Private",156.1277687,"Private",31/03/2013,0},
{13,FALSE,,"M","Private",,,,,,"UK",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Private",1093.030333,"Private",29/02/2016,3},
{14,FALSE,,"FF","Company",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"AG",02/01/2017 02:36,,01/11/2016 12:05,01/11/2016 12:05,,,,,"Active",,,"Equity",,"","",,"Other","Company",2394.249123,"Company",31/03/2013,0},
{15,FALSE,,"N","Private",,,,,,"",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,28/11/2016 09:15,28/11/2016 09:15,,,,,"Active",,,"Equity",,"","",,"Other","Private",782.6398577,"Private",30/04/2016,5},
{16,FALSE,,"AA","Company",,,,,,"Israel",,,,,,,,,,,,,,,"y",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,11/12/2016 16:44,11/12/2016 16:44,,,,,"Active",,,"Equity",,"","",,"Other","Company",5426.93198,"Company",31/07/2016,8},
{17,FALSE,,"AB","Company",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Company",806.5134905,"Company",30/04/2016,5},
{18,FALSE,,"AC","Company",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",04/01/2017 16:09,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Company",5468.522038,"Company",31/08/2016,9},
{19,FALSE,,"AD","Private",,,,,,"Israel",,,,,,,,,,,,,,,"y",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,22/11/2016 11:27,22/11/2016 11:27,,,,,"Active",,,"Equity",,"","",,"Other","Private",260.0739437,"Private",31/10/2016,11},
{20,FALSE,,"AE","Private",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,20/12/2016 07:56,20/12/2016 07:56,,,,,"Active",,,"Equity",,"","",,"Other","Private",717.2395009,"Private",31/01/2016,2},
{21,FALSE,,"AF","Private",,,,,,"Israel",,,,,,,,,,,,,,,"y",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Private",525.2413493,"Private",31/08/2016,9},
{22,FALSE,,"AG","Private/ Family office",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Family Office",518.01409,"Family Office",30/04/2016,5},
{23,FALSE,,"AH","Company",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,11/12/2016 16:37,11/12/2016 16:37,,,,,"Active",,,"Equity",,"","",,"Other","Family Office",525.2413493,"Family Office",31/08/2016,9},
{24,FALSE,,"AI","Private",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",01/11/2016 12:04,"g",23/11/2016 16:52,,22/11/2016 13:13,22/11/2016 13:13,,,,,"Active",,,"Equity",,"","",,"Other","Private",0,"Private",31/05/2016,6},
{25,FALSE,,"BB","Private",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,11/12/2016 16:50,11/12/2016 16:50,,,,,"Active",,,"Equity",,"","",,"Other","Family Office",518.0244503,"Family Office",30/04/2016,5},
{26,FALSE,,"BC","Private",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Private",1518.733965,"Private",31/07/2016,8},
{27,FALSE,,"BD","Company",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Other","Company",207.205636,"Company",30/04/2016,5},
{28,FALSE,,"DD","Private",,,,,,"",,,,,,,,,,,,,,,"AQ",24/11/2016 07:43,"A",20/12/2016 07:55,"g",21/12/2016 16:10,,20/12/2016 08:00,20/12/2016 08:00,,,,,"Active",,,"Equity",,"yes","C",,"Israel","Private",260.3472779,"Private",30/11/2016,12},
{29,FALSE,,"EE","Private",,,,,,"Israel",,,,,,,,,,,,,,,"AQ",01/11/2016 11:07,"A",27/12/2016 07:13,"g",27/12/2016 07:13,,01/11/2016 12:04,01/11/2016 12:04,,,,,"Active",,,"Equity",,"","",,"Israel","Family Office",260.844415,"Family Office",31/03/2016,4}
})

kind regards,

Ronen

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,
Angelia

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.