Forum Discussion
Power Query Structured lookup
Hi,
Can someone help or at least point me in direction of information on a structured lookup;
example position operator table { 0 } , so the starting point is [ A = 1 = [A = 2]] = a true / false ,
so I have a fact table ;
Table.FromRecords(
{ [Item = "A"], [Item = "B"], [Item = "C"],[Item = "D"],
[Item = "A"] , [Item = "C"] , [Item = "C"] , [Item = "D"] } )
and a lookup
Table.FromRecords( { [Item = "A", Unit = 2],
[Item = "B", Unit = 3],
[Item = "C", Unit = 4],
[Item = "D", Unit = 5]} )
so I can lookup a value ;
Table.AddColumn( Fact, "N", each lookup { [Item = [Item]] ))
This returns the correct corresponding row as a record ; but I can't get to grips with how it works, as it is I presume returnng a true/false as a list? not sure,
This is the code for what I have as example ;
= [
lookupT = Table.FromRecords( { [Item = "A", Unit = 2],
[Item = "B", Unit = 3],
[Item = "C", Unit = 4],
[Item = "D", Unit = 5]} ),
Fact = Table.FromRecords(
{ [Item = "A"], [Item = "B"], [Item = "C"],[Item = "D"],
[Item = "A"] , [Item = "C"] , [Item = "C"] , [Item = "D"] } ) ,
Addcol = Table.AddColumn( Fact, "N", each
lookupT { [Item = [Item]]} )]
Richard.
There are some more examples that may confuse you. If you can understand them, then you understand "field access" (and projection) and the keyword each.
// Example 1 [A = 1, B = 2][[A], [C]]? // Example 2 #table({"A", "B"}, {{1, 2}, {1, 3}})[[D]]? // Example 3 let _ = [ A = 1, B = 2 ] in [A] // Example 4 let _ = [ A = 1, B = 2 ] in _[[A], [C]]?
4 Replies
This is a native syntax that means find the only row in the table that meets the conditions.
You can find its usage in the Power Query documentation.
https://learn.microsoft.com/en-us/powerquery-m/m-spec-operators#selection-and-projection-operators
For example, tbl{[A=1, B=2]) means to find the row in table tbl where column A is equal to 1 and column B is equal to 2 (return a record value).
You may know that the operator ? can prevent errors when an item or field does not exist, but it may not work here. The operator ? can only avoid not being found. If multiple rows that meet the conditions are found, an error will still be returned.
// null Table.FromValue({1, 2, 3, 3}){[Value = 4]}? // error Table.FromValue({1, 2, 3, 3}){[Value = 3]}?Thanks I'll have a look,
I'll leave this open for a bit and see if anyone else has reading suggestions.
Richad.just to add having had al look , I knew using Table [ [ Column] ] was projection, but would not have thought to search for that so i've learned something already.
There are some more examples that may confuse you. If you can understand them, then you understand "field access" (and projection) and the keyword each.
// Example 1 [A = 1, B = 2][[A], [C]]? // Example 2 #table({"A", "B"}, {{1, 2}, {1, 3}})[[D]]? // Example 3 let _ = [ A = 1, B = 2 ] in [A] // Example 4 let _ = [ A = 1, B = 2 ] in _[[A], [C]]?
