FIND Formula by customer account number (hierarchy)

[gmasta1129]

Hello,

I want to create a new column in Power BI. 

Please see picture below for reference. 

My new column should look at the cust_Ac_No column ( there are thousands of account numbers. My screenshot is only showing 1 account). 

After looking at the cust_AC_No, then find the word "CRDM" in the Prod column.  If its found then pull in CRDM for each row containing the Cust_Ac_No (in this example it is 30000USD01), if it is not found then look for "CRDI", if its found then pull in CRDI.  If there is no CRDM or CRDI, then pull in "Blank"

The hierarchy is find CRDM first, if not then find CRDI, if neither then blank

Please see "new column" in screenshot below for the output I expect to see.  Since CRDM can be found for cust_ac_no 3000USD01, then pull CRDM in all rows containt 30000USD01.  

[gmasta1129]

@Anonymous Thank you so much for your help! I entered Solution#2 into Power BI desktop and it worked perfectly. 

[dufoq3]

Hi @gmasta1129, Power Query solution:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYwMFDSUXJ0clYIcAwK8XMNCgZynYNcPJVidciX9qWZtEuQZwgeaU9nX4i0JRAA+b6OwSGuQYbIjiZBxtHREbuEk5MTDrOcnbFLwN2NIQN3sjkQAPnBPo5hrkZkScDciy4Ocy66OMy16OIuLi54LI4FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cust_No = _t, Customer_Name = _t, Prod = _t]),
    GroupedRows = Table.Group(Source, {"Cust_No"}, {{"ProdMaster", each if List.Contains([Prod], "CRDM") then "CRDM"else if List.Contains([Prod], "CRDI") then "CRDI" else null, type table}}),
    MergedQueries = Table.NestedJoin(Source, {"Cust_No"}, GroupedRows, {"Cust_No"}, "GroupedRows", JoinKind.LeftOuter),
    ExpandedGroupedRows = Table.ExpandTableColumn(MergedQueries, "GroupedRows", {"ProdMaster"}, {"ProdMaster"})
in
    ExpandedGroupedRows

[Anonymous · Accepted solution]

Hi,

@lbendlin , Thanks for your concern about the problem, and i want to offer some more information for user to refer to.

hello @gmasta1129 , you can refer to the following solution.

Sample data 

Solution 1:

Power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtU3NtQ3MjAyUdJRMjYwMAgNdjEwBLKdg1w8lWJ1KFPgS5kCFyfPEBoqMKJYgTGhcDAmxgSIglgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [RunDate = _t, Cust_Ac_No = _t, Prod = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RunDate", type date}, {"Cust_Ac_No", type text}, {"Prod", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let 
a=Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[Cust_Ac_No]=[Cust_Ac_No] and x[Prod]="CRDM")),b=Table.RowCount(Table.SelectRows(#"Changed Type",(x)=>x[Cust_Ac_No]=[Cust_Ac_No] and x[Prod]="CRDI"))
in if a>0 then "CRDM" else if b>0 then "CRDI" else null)
in
    #"Added Custom"

Solution 2:
Create a new calculated column.

Column =
VAR _cdrm =
    COUNTROWS (
        FILTER (
            'Table',
            [Cust_Ac_No] = EARLIER ( 'Table'[Cust_Ac_No] )
                && [Prod] = "CRDM"
        )
    )
VAR _crdi =
    COUNTROWS (
        FILTER (
            'Table',
            [Cust_Ac_No] = EARLIER ( 'Table'[Cust_Ac_No] )
                && [Prod] = "CRDI"
        )
    )
RETURN
    IF ( _cdrm > 0, "CRDM", IF ( _crdi > 0, "CRDI" ) )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...