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