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 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 High160020000
Very High2150600
High375150
Medium44875
Low5048
Not Defined6  
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.

5 Replies

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