Forum Discussion
Add custom column with multiple criteria
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 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...
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...
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.
How do i do it when I am only in Power Query?
Sorry if noob question