Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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...
Proud to be a PBI Community Champion
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.
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
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" ) )
@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...
Proud to be a PBI Community Champion
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.
Proud to be a PBI Community Champion
How do i do it when I am only in Power Query?
Sorry if noob question
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?
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |