Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
gmasta1129
Helper III
Helper III

FIND Formula by customer account number (hierarchy)

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.  

 

Screenshot 2024-06-10 100452.png

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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 

vxinruzhumsft_0-1718160867751.png

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

vxinruzhumsft_1-1718161025266.png

 

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.

View solution in original post

4 REPLIES 4
gmasta1129
Helper III
Helper III

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

dufoq3
Super User
Super User

Hi @gmasta1129, Power Query solution:

 

Result

dufoq3_0-1718176154433.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

v-xinruzhu-msft
Community Support
Community Support

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 

vxinruzhumsft_0-1718160867751.png

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

vxinruzhumsft_1-1718161025266.png

 

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
Super User
Super User

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...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors