Matching Values Between Two Tables and Displaying Them

[thmonte]

I am trying to pull two tables together into some visuals and I am having a hard time.

Here is an example dataset:

Table 1
Date	ID	Type
1/1/2018	001	1065
1/1/2018	002	1065
1/1/2018	003	1065
1/1/2018	004	1065
1/2/2018	001	1065
1/2/2018	006	1065
Table 2
Date	ID	Work	Work_Attrib
1/1/2018	001	Work Type A
1/1/2018	001	Work Type B	OT
1/1/2018	002	Work Type B
1/1/2018	003	Work Type B
1/1/2018	004	Work Type C
1/1/2018	004	Work Type A
1/2/2018	001	Work Type C
1/2/2018	006	Work Type B

I'd like to get one regular table visual to display the following

Visual 1
Date	ID	Type	Work Type
1/1/2018	001	1065	Work Type B
1/1/2018	002	1065	Work Type B
1/1/2018	003	1065	Work Type B
1/1/2018	004	1065	Work Type A
1/2/2018	001	1065	Work Type C
1/2/2018	006	1065	Work Type B

You'll notice each ID has multiple work types in Table 2 but I need a unique 1 for the visual so here are some rules around it

• If there are multiple values in table two for an ID for a day AND there is one of them with OT as a attrib then use that one in the visual
• If there are multiple values in table two for an ID for a day AND none of them have OT then take the latest one
• If there are multiple values in table two for an ID for a day AND more than one has a attrib with OT then take the latest out of OT for the visual

The second type of a visual will be a sum of these work types that are being displayed in Visual 1

Visual 2
Date	Work Type A	Work Type B	Work Type C
1/1/2018	1	3	0
1/2/2018	0	1	1

Hope you all can help me!  Thanks

[LivioLanzo]

@thmonte

You can do this easily work Power Query. After having loaded Table1 and Table2 as two different queries, create this Query:

let
    Source = Table.NestedJoin( Table1, {"Date", "ID"}, Table2, {"Date", "ID"}, "JoinedTable", JoinKind.Inner ), 
    
    fnGetRow = ( tbl as table ) as table =>
       let
           FilterOT = Table.SelectRows( tbl, each [Work_Attrib] = "OT" ),
           SelectedTable  = if Table.RowCount( FilterOT ) = 0 then tbl else FilterOT,
           GetLastRow = Table.LastN( SelectedTable, 1 )
       in
           GetLastRow,
           
   TransformJoinTable = Table.TransformColumns( Source, {"JoinedTable", fnGetRow, type table}),
   
   ExpandTable = Table.ExpandTableColumn( TransformJoinTable, "JoinedTable", {"Work"} )
           
in
    ExpandTable

 

[thmonte]

THis is pretty good!  I do most of my work in DAX but seems like PowerQuery might be something I want to dive into as well.

I have a question tho.

It looks like if there is no match in the second database (no Work Type is found for that ID) that row records is not included in the power query.  Is it possible to tweak this?  And have all records from Table 1 show and if nothing is found in Table 2 to populate work column then make it a static value like "No Work Found"

[thmonte]

@LivioLanzo

[LivioLanzo]

@thmonte

Yes, you can do it like this:

let
    Source = Table.NestedJoin( Table1, {"Date", "ID"}, Table2, {"Date", "ID"}, "JoinedTable", JoinKind.LeftOuter ), 
    
    fnGetRow = ( tbl as table ) as table =>
       let
           FilterOT = Table.SelectRows( tbl, each [Work_Attrib] = "OT" ),
           SelectedTable  = if Table.RowCount( FilterOT ) = 0 then tbl else FilterOT,
           GetLastRow = if
                           Table.IsEmpty( SelectedTable )
                        then
                           Table.FromRecords( { [Work = "No Work Found"] } )
                        else
                            Table.LastN( SelectedTable, 1 )
       in
           GetLastRow,
           
   TransformJoinTable = Table.TransformColumns( Source, {"JoinedTable", fnGetRow, type table}),
   
   ExpandTable = Table.ExpandTableColumn( TransformJoinTable, "JoinedTable", {"Work"} )
           
in
    ExpandTable