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.

     

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
9 years ago

OK, just so I have this straight. GroupCodes start with having an S extension. Changes happen and they continue to have the S extension. Then, for some GroupCodes, they end or something and at that point they are given an X extension. You want to identify the ones that have a change from S to X and those that never change and have all S entries and no X entries. Is that correct?

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