cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
alepage Member
Member

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  
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: M Query lookupvalue

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.

View solution in original post

5 REPLIES 5
Nathaniel_C Super Contributor
Super Contributor

Re: M Query lookupvalue

Hi @alepage ,

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

else if.PNG

 

alepage Member
Member

Re: M Query lookupvalue

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,

Community Support Team
Community Support Team

Re: M Query lookupvalue

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.

View solution in original post

alepage Member
Member

Re: M Query lookupvalue

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

Re: M Query lookupvalue

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.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)