Forum Discussion

thmonte's avatar
thmonte
Helper IV
7 years ago

Matching Values Between Two Tables and Displaying Them

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/20...
Need Help
thmonte's avatar
thmonte
Helper IV
7 years ago

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"

  • LivioLanzo's avatar
    LivioLanzo
    Solution Sage
    7 years ago

    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