Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to 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. |
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. |
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |