Forum Discussion
Post ProdigyPower 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
ZhangKunSuper User
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]}?
DickenThanks I'll have a look,
I'll leave this open for a bit and see if anyone else has reading suggestions.
Richad.- Dicken
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.
- ZhangKun
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]]?