Forum Discussion
Solved
Lookup and return value at each level
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 ...
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
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"
