Forum Discussion
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
4 Replies
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"Hello Anonymous
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!
pbi
here is the pbix file i was working on..
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
