Search & return values within the same table

[PremierPBI]

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.

[PremierPBI]

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 · Accepted solution]

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]

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

[dufoq3]

You're welcome. Just one note: you should replace both #"Changed Type" in my query, not only the first one.

[dufoq3]

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]

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]

Have you read note bellow my posts?