Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

M Query lookupvalue

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 t...
Need Help
  • dax's avatar
    dax
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
dax
Community Support
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago
    Hello, Thank for this information. I will test your code today or tomorrow. I will let you know if it works. Thank again.
    • dax's avatar
      dax
      Community Support
      6 years ago

      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.