Forum Discussion

majdkaid22's avatar
majdkaid22
Helper V
9 years ago
Solved

Is Group changed using If

Hi guys,   I have the below data, recording every change happen on trading account concerning the risk model, which is the represented in the (GroupCode)   Every change is recorded, and several c...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    9 years ago

    OK, this is a bit brute force, but I created an Enter Data query:

     

    GroupCodes

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg120XX01Q12c/EM0g1W0lEyNNA3MtE3MjA0U4rVwSFvTEDeiIC8IQF5AxzyERB5Q0sC8haY8iHBAQZweXMs+j2dDI1gDjA0w6UAZoIpFhuQXYAtBFEMwBaEKE6AhWEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [GroupCode = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"GroupCode", type text}, {"Date", type date}})
in
    #"Changed Type"

    I then created these custom columns in DAX:

    Group = LEFT([GroupCode],LEN([GroupCode])-2)

Code = RIGHT([GroupCode],1)

HasX = IF([Code]="X",1,0)

HasS = IF([Code]="S",1,0)

    Then you can put Group, HasX and HasS in a table visual and potentially filter by 0's to get whatever combination that you want.

     

     

majdkaid22's avatar
majdkaid22
Helper V
9 years ago

Greg_Deckler That is very much true Sir!

 

I want to identify the ones that have a change from S to X, or vice versa, and those that never change and have all S entiries or X entiries

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

OK, this is a bit brute force, but I created an Enter Data query:

 

GroupCodes

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCg120XX01Q12c/EM0g1W0lEyNNA3MtE3MjA0U4rVwSFvTEDeiIC8IQF5AxzyERB5Q0sC8haY8iHBAQZweXMs+j2dDI1gDjA0w6UAZoIpFhuQXYAtBFEMwBaEKE6AhWEsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [GroupCode = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"GroupCode", type text}, {"Date", type date}})
in
    #"Changed Type"

I then created these custom columns in DAX:

Group = LEFT([GroupCode],LEN([GroupCode])-2)

Code = RIGHT([GroupCode],1)

HasX = IF([Code]="X",1,0)

HasS = IF([Code]="S",1,0)

Then you can put Group, HasX and HasS in a table visual and potentially filter by 0's to get whatever combination that you want.

 

 

  • majdkaid22's avatar
    majdkaid22
    Helper V
    9 years ago

    Greg_Deckler appreciate you taking the time to demonstrate the case for me!

     

    I know very basic info when it comes to data query. 

     

    I opened up my Advanced Editor and tried to paste your proposed Data query, but am keep getting an error. 

     

    Obviously am not writing the query the correct way. 

     

     

    Below is my current query for meAccountHistory table.  

     

    How should I add your query to it?

     

    let
    Source = Sql.Databases("102.222.13.15"),
    ODS = Source{[Name="ODS"]}[Data],
    dbo_meAccountHistory = ODS{[Schema="dbo",Item="meAccountHistory"]}[Data],
    #"Replaced Value" = Table.ReplaceValue(dbo_meAccountHistory,null,"0",Replacer.ReplaceValue,{"AgentAccountNumber"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"Unknown",Replacer.ReplaceValue,{"AgentAccountName"})
in
    #"Replaced Value1"

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi majdkaid22,

       

      You shouldn't add smoupre's query, it means these data is load from excel(your data is load from sql). You need to create calculate columns and create the visual.

       

      Regards,

      Xiaoxin Sheng

      • majdkaid22's avatar
        majdkaid22
        Helper V
        9 years ago

        Anonymous I did that first thing, but am getting the following error for the below column when I try to create it

         

        "An argument of function 'LEFT' has the wrong data type or has an invalid value"

         

         

        Group = LEFT([GroupCode],LEN([GroupCode])-2)

         

        I assume it's the data type of [GroupCode], but I cannot know what to change it to.

         

        Thanks,

        Majd