Connecting different Data Sources

[ethanlsaul]

Hello,

I have 2 different data sources/tables. 1 Table looks like this:

Product	Days of Supply	Owner
Pencil	10	Ethan
Book	3	Joe
Paper	1	Joe
Marker	18	Todd
Pen	6	Todd
Folder	5	Ethan

Table 2 is a similar table, but only contains 2 of the items in the first table and it shows the status.

Product	Status
Pencil	Out of Stock
Marker	Out of Stock

I want the first data set to look into table 2 and show the status. If the item isnt in the table 2, it have something like "In stock" or N/A or some other general saying everytime.

Product	Days of Supply	Owner	Status
Pencil	10	Ethan	Out of Stock
Book	3	Joe	N/a
Paper	1	Joe	N/a
Market	18	Todd	Out of Stock
Pen	6	Todd	N/a
Folder	5	Ethan	N/a

[AlB · Accepted solution]

Hi @ethanlsaul 

This can be best done in Power Query with a merge. Place the following M code in a blank query to see the steps.

See it all at work in the attached file.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkjNS87MUdJRMjQAEq4lGYl5SrE60UpO+fnZQAFjIPbKTwULBSQWpBaBVCKJ+SYWZUMELYBESH5KCkRpah6Qa4Ys5JafkwJWaYqwJxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, #"Days of Supply" = _t, Owner = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Days of Supply", Int64.Type}, {"Owner", type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Product"}, Table2, {"Product"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Status"}, {"Status"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Table2",null,"N/A",Replacer.ReplaceValue,{"Status"})
in
    #"Replaced Value"

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.