Add custom column with multiple criteria

[johnphil]

Hi Everyone,

I am looking for ways to add a new column for the table using the criteria set below. Appreciate your help on this. Thank you.

Table

Criteria

[Thim]

Here is my take on it.

Dublicate the BY Ty column under Edit Queries

change the format to whole numbers (This will error all letters)

Change all errors to 0.

End Edit Queries.

Now type this formula.

Identifier = IF('Table'[BU Ty - Copy]=0;1;IF('Table'[BU Ty - Copy]<30;3;IF('Table'[BU Ty - Copy]>39;3;2)))

This should give you, the wanted result.

[Anonymous]

As far as I understand it. There's no easy way to do it. You'll need to actually create a table that Holds each BU Type. Example

[Anonymous]

You can create a calcuated column and try the following DAX

Identifier =
IF (
    SEARCH ( "3", 'Type'[BU Type],, 0 ) = 1,
    "2",
    IF ( ISERROR ( VALUE ( 'Type'[BU Type] ) ), "1", "3" )
)

[PattemManohar · Accepted solution]

@johnphil You need to have bit transforming to your lookup table before you use that. This can be achieved in the "Power Query Editor" as below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnTUjYpS0lEyVIrViVYyNtA1tgTyjMA8AwNdI0sdEwNdS5CYsVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [BuType = _t, Identifier = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"BuType", type text}, {"Identifier", Int64.Type}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"BuType", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "BuType"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"BuType", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "BuType", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"BuType.1", "BuType.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"BuType.1", type text}, {"BuType.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"BuType.1", "BuTypeLower"}, {"BuType.2", "BuTypeHigher"}})
in
    #"Renamed Columns"

Now it will look like this...

Then, you can use this lookup table (Which was transformed as above) in your main table as below

Column = CALCULATE(VALUES(Test30Lkp[Identifier]),FILTER(Test30Lkp,Test30CalcField[BUType]>=Test30Lkp[BuTypeLower] && Test30CalcField[BUType] <= Test30Lkp[BuTypeHigher]))

Here is the expected output...

 

[johnphil]

@PattemManohar

Tried this one by doing the following:

=CALCULATE (VALUES(Query1[Identifier]),FILTER(Query1, 'BU Type'[BU Ty] >= Query1[BuTypeLower] && 'BU Type'[BU Ty] <= Query1[BuTypeHigher]))

But I got this error:

"Calculation error in measure 'BU Type'[BU Type Identifier]: A single value for column 'BU Ty' in table 'BU Type' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

Don't know if I did it the way I should have...

[PattemManohar]

@johnphil It was a calculated column based on lookup table. You are trying to create a measure. Also, please make sure you have transformed the lookup table as mentioned above otherwise your lookupvalue function will fail.

[johnphil]

How do i do it when I am only in Power Query?

Sorry if noob question

[PattemManohar]

@johnphil If you want to do this Power Query, then you need to write a custom LOOKUP function using M-Code.

[Anonymous]

Hi @johnphil,

Can you elaborate more on what you need? Is the Criteria, values between AA and ZZ should have identifier 1, values between 30 and 39 should have identifier 2 etc.? And the new column you need is an identifier column?

[johnphil]

Hi @Anonymous

"Is the Criteria, values between AA and ZZ should have identifier 1, values between 30 and 39 should have identifier 2 etc.? " Yes.

"And the new column you need is an identifier column?" Yes. This new column should have values like 1, 2 or 3.