Power Query Custom Column Lookup Value From Columns In Another Table

[Anonymous]

I want to create 2x custom columns that will lookup an item id from table 1 and return the read scan date based on conditions of table 2 columns. So the lookup logic for the custom columns should be:

• custom column A - return MIN (earliest) Read Scan Date if Site ID = "NZAKLA" and Reader Desc = "Reader at the office entrance". If item has NO Read Scan Date at the office entrance, then return "No Scan"
• custom column B - return MAX (last) Read Scan Date if Site ID = "NZAKLA" and Reader Desc = "Reader at the office exit". If item has NO Read Scan Date at the office exit, then return "No Scan"

TABLE 1

Item ID	Custom Column A (NZAKLA Entrance Scan)	Custom Column B (NZAKLA Exit Scan)
201502869

TABLE 2

Item ID	Read Scan Date	Site ID	Reader Desc
201502869	21/04/2020 13:44	NZAKLA	Reader at the office entrance
201502869	21/04/2020 13:44	NZAKLA	Reader at the office entrance
201502869	21/04/2020 13:48	NZAKLA	Reader at the office entrance
201502869	21/04/2020 14:05	NZAKLA	Reader at office entrance and exit
201502869	21/04/2020 14:06	NZAKLA	Reader at office entrance and exit
201502869	21/04/2020 14:07	NZAKLA	Reader at the customs entrance
201502869	21/04/2020 16:25	NZAKLA	Reader at the office exit
201502869	21/04/2020 17:38	NZAKL2	Reader at the office entrance
201502869	22/04/2020 0:57	NZAKL2	Process
201502869	23/04/2020 0:31	NP0003	Reader at the office exit
201502869	24/04/2020 22:20	NP0003	Reader at the office exit

[AlB]

@Anonymous 

#"Added Custom" and #"Added Custom2" are the steps you are looking for. Assumes Table2 as shown in your example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNDUwsjCzVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item ID", Int64.Type}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom Column A (NZAKLA Entrance Scan)", each let res_=List.Min(Table.SelectRows(Table2, (inner)=> inner[Item ID] = [Item ID] and inner[Site ID]="NZAKLA" and inner[Reader Desc]="Reader at the office entrance")[Read Scan Date]), output_=if res_= null then "No scan" else res_ in output_),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Custom Column B (NZAKLA Exit Scan)", each let res_=List.Max(Table.SelectRows(Table2, (inner)=> inner[Item ID] = [Item ID] and inner[Site ID]="NZAKLA" and inner[Reader Desc]="Reader at the office exit")[Read Scan Date]), output_=if res_= null then "No scan" else res_ in output_)

in
    #"Added Custom2"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi @AlB, thanks that worked however the calculation / refresh takes a very long time as I assume its due to the size of table 2 with 50k rows....and I intend to add more custom columns with similar formula that you provided which may break the report.

Is there another way to make this more efficient?

[AlB]

@Anonymous 

Not sure. I'd need the full data to play a bit with it. If you don't wan to make it public you can share the link by private message

Have you tried to do this in DAX, creating a calculated table? I believe it would be faster

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @Anonymous 

I haven't yet had much time to review the M code. Perhaps @ImkeF, @edhans have ideas on how to make it faster.

In any case you can try in DAX as well:

1. Build a query in M that returns just the "Item ID" column (like the first column only in the first table you showed) and load it onto the data model, name "Flat File data"

2. Create calculated columns on the "Flat File data" table:

=
VAR res_ = CALCULATE (
    MIN ( 'Raw RFID Data 1'[Read_Dt] );
    FILTER (
        ALL ( 'Raw RFID Data 1'[Item_Id] );
        'Raw RFID Data 1'[Item_Id] = 'Flat File Data'[Item_Id]
    );
    'Raw RFID Data 1'[Reader_Pse] = "Reader at the office entrance";
    'Raw RFID Data 1'[Site_Id] = "NZAKLA"
)
RETURN 
IF(ISBLANK(res_); "No scan";res_)

=
VAR res_ = CALCULATE (
    MIN ( 'Raw RFID Data 1'[Read_Dt] );
    FILTER (
        ALL ( 'Raw RFID Data 1'[Item_Id] );
        'Raw RFID Data 1'[Item_Id] = 'Flat File Data'[Item_Id]
    );
    'Raw RFID Data 1'[Reader_Pse] = "Reader at the office exit";
    'Raw RFID Data 1'[Site_Id] = "NZAKLA"
)
RETURN 
IF(ISBLANK(res_); "No scan";res_)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

@AlB thank you it works and much quicker execution/load time, however the RETURN does not work as intended i.e. if Read_Dt blank, then return "No Scan", instead its returning blank. Your thoughts?

Note: I had to wrap function with FORMAT date time as there was an error without it.

[ImkeF]

Hi @Anonymous ,

this code should run faster:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNDUwsjCzVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item ID", Int64.Type}}),
    FilterForCol1 = Table.SelectRows(Table2, each ([Site ID] = "NZAKLA") and ([Reader Desc] = "Reader at the office entrance")),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Item ID"}, FilterForCol1, {"Item ID"}, "FilterForCol1", JoinKind.LeftOuter),
    AddColA = Table.AddColumn(#"Merged Queries", "ColA", each List.Min([FilterForCol1][Read Scan Date])),
    FilterForColB = Table.SelectRows(Table2, each ([Site ID] = "NZAKLA") and ([Reader Desc] = "Reader at the office exit")),
    #"Merged Queries1" = Table.NestedJoin(AddColA, {"Item ID"}, FilterForColB, {"Item ID"}, "FilterForColB", JoinKind.LeftOuter),
    AddColB = Table.AddColumn(#"Merged Queries1", "ColB", each List.Max([FilterForColB][Read Scan Date])),
    #"Replaced Value" = Table.ReplaceValue(AddColB,null,"No Scan",Replacer.ReplaceValue,{"ColA", "ColB"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"FilterForCol1", "FilterForColB"})
in
    #"Removed Columns"

Problem with the other code is that the whole Table2 will be adressed with every row of your Table1.

I'm using 2 merges here, so the Table2 will just be reference twice.

[AlB]

Hi @Anonymous 

Can you provide an example based on your dat showing the expected result?  For instance, what would the expected result be for ID 201502869 and the rationale?

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Anonymous]

Hi @AlB, so for the sample data provided, the expected results for item ID 201502869 should be:

• for column A (office entrance scan) is 21/04/2020 13:44
• for column B (office exit scan) is 21/04/2020 16:25

Hope that makes sense...