Forum Discussion

PremierPBI's avatar
PremierPBI
Regular Visitor
2 years ago
Solved

Search & return values within the same table

Hi All 

I have a very simple table listing rebate IDs & rebate term percentages

Some rebate IDs are stand alone, but some IDs have 'Parent Group IDs'

 

I want to add a new column that looks at 'PARENT_REBATE_GROUP_ID' if the value is 'zero' I want to return 'REBATE_TERMS_PERCENTAGE' if the 'PARENT_REBATE_GROUP_ID' has an 'ID' I want to return the 'REBATE_TERMS_PERCENTAGE' assigned to that line. Please see the visual below.

 

 

Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    In your query RABATE_GROUPS

    1. select last step
    2. create new step via circled button
    3. place there code below
    4. replace #"Changed Type" with your previous step reference.

     

     

    = Table.AddColumn(#"Changed Type", "Custom", each if [PARENT_REBATE_GROUP_ID] = 0 then [REBATE_TERMS_PERCENTAGE] else #"Changed Type"{[ID = [PARENT_REBATE_GROUP_ID]]}[REBATE_TERMS_PERCENTAGE], type number)

     

7 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi PremierPBI,

    for future requests provide sample data as table (no a screenshot) so we can copy/paste and also expected result based on sample data please.

     

    Result

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIAYkMDpVidaCUjKNcIwjWGco0hXBOQDBCbQLimIH1AbArhmoEUArEZkBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, PARENT_REBATE_GROUP_ID = _t, REBATE_TERMS_PERCENTAGE = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"PARENT_REBATE_GROUP_ID", Int64.Type}, {"REBATE_TERMS_PERCENTAGE", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [PARENT_REBATE_GROUP_ID] = 0 then [REBATE_TERMS_PERCENTAGE] else #"Changed Type"{[ID = [PARENT_REBATE_GROUP_ID]]}[REBATE_TERMS_PERCENTAGE], type number)
in
    #"Added Custom"
    • PremierPBI's avatar
      PremierPBI
      Regular Visitor
      2 years ago

      Hi dufoq3,

       

      Thank you so much for responding, I am happy you have found a solution. I am not confident of where to place this code.

      I am so sorry I have taken another screenshot below that shows you the name of the table 'REBATE_GROUP'

      I want the column to be named 'MainRebate'

       

      Could you paste the code I would put in the box below to make your solution work?

       

      Thank you in advanced.

       

       

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        Have you read note bellow my posts?

  • PremierPBI's avatar
    PremierPBI
    Regular Visitor
    2 years ago

    I have, sorry this is my first post.

     

    Maybe this is a step up for my Power Bi level. I need layman's terms 🙂

     

    • dufoq3's avatar
      dufoq3
      Community Champion
      2 years ago

      In your query RABATE_GROUPS

      1. select last step
      2. create new step via circled button
      3. place there code below
      4. replace #"Changed Type" with your previous step reference.

       

       

      = Table.AddColumn(#"Changed Type", "Custom", each if [PARENT_REBATE_GROUP_ID] = 0 then [REBATE_TERMS_PERCENTAGE] else #"Changed Type"{[ID = [PARENT_REBATE_GROUP_ID]]}[REBATE_TERMS_PERCENTAGE], type number)

       

      • PremierPBI's avatar
        PremierPBI
        Regular Visitor
        2 years ago

        dufoq3, thank you so much for your help and patience, I have it working now.🙏