M Query lookupvalue

[Anonymous]

Hello,

I have two tables, let's say Table A and Table B.  Table B is showned below.

In table A, I have the variable Estimated Hours of Work.  I would like to add to Table A, the level index of table B, by using the min and max value of table B.

Ex:  If the estimated Hours of work from Table A is 60 hours. Then 60 is greather than 48 and less then 75, so the Level Index will be 4.  This value will be bring into table A.

So my Question is how can we import the Level Index of table B into Table A using M language.

Regards,

LOE                     level Index    Min  Max

Extremely High	1	600	20000
Very High	2	150	600
High	3	75	150
Medium	4	48	75
Low	5	0	48
Not Defined	6

[Nathaniel_C]

Hi @Anonymous ,

In M, I would add a column and use an if as in the picture...only partially done.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

[Anonymous]

Hello,

I could also use a switch function to do the same job.  It is not what i would like to do.

I want to use the min and max value of table B to select the level index.

In that manner, if for some reason the definition of Level index change, I Will need to update the value of table B, not the dax

Regards,

[dax · Accepted solution]

Hi alepage,

You could try below M code(The T3 is your table B)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVYyMgBTxhDKDEKZQygLCGUJoQyhlKlSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"amount", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each T3),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"LOE", "level index", "min", "max"}, {"Custom.LOE", "Custom.level index", "Custom.min", "Custom.max"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Custom",null,-1,Replacer.ReplaceValue,{"amount"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,-1,Replacer.ReplaceValue,{"Custom.min"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",null,-1,Replacer.ReplaceValue,{"Custom.max"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value2", "Custom", each if [amount]>=[Custom.min] and [amount]<[Custom.max] or [amount]=[Custom.min] and [amount]=[Custom.max] then [Custom.level index] else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] <> 0))
in
    #"Filtered Rows"

Best Regards,
Zoe Zhi

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

[Anonymous]

Hello, Thank for this information. I will test your code today or tomorrow. I will let you know if it works. Thank again.

[dax]

Hi alepage,

May I know how is your issue going currently? If your issue is solved please mark the appropriate answer as Mark as answer. This will help other members to find a solution if they face the same issue. If you still have any questions, please feel free to ask.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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