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.

Nathaniel_C's avatar
Nathaniel_C
Community Champion
6 years ago

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'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.