Forum Discussion
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...
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
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"
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