Lookup and return value at each level

[topazz11]

Hello,

I want to create a new table that has a column which lookups and returns each value at each level from lookup table.

Is it possible to do this using Power Query?

Thank you

[Vera_33]

Hi @topazz11 

What is the logic when you do lookup? I tried different ways, but never came up what you wanted. If Limit <= Amount, then

I have a table called LookupTable. And pls do provide the logic and sample data in a format which people can copy in the future

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIxNTBQitWJVnICcoyNoRxnIMfIEMpxAXIsQexYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Amount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Amount", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", (x)=> Table.SelectRows(LookupTable, each  Number.From(Text.Select( [Limit],{"0".."9"}))<=  x[Amount])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Level", "Name"}, {"Level", "Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([Level] <> null)),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Level]), "Level", "Name")
in
    #"Pivoted Column"

[topazz11]

pbi

here is the pbix file i was working on..

[Vera_33 · Accepted solution]

Hi @topazz11 

I don't think I completely understand your logic...you have different comparison for Type and Model, here is the original way with your first sample data

[topazz11]

Hello @Vera_33 

The logic is just the amount fall under the limit so if the amount is 2100 then it can go up to the limit 3000.

your screen shot looks correct...can you show me the lookup table you used?

Thank you!