Forum Discussion
Connecting different Data Sources
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 |
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.
1 Reply
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.