Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ethanlsaul
Helper I
Helper I

Connecting different Data Sources

Hello,

 

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

 

ProductDays of SupplyOwner
Pencil10Ethan
Book3Joe
Paper1Joe
Marker18Todd
Pen6Todd
Folder5Ethan

 

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

 

ProductStatus
PencilOut of Stock
MarkerOut 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.

 

ProductDays of SupplyOwnerStatus
Pencil10EthanOut of Stock
Book3JoeN/a
Paper1JoeN/a
Market18ToddOut of Stock
Pen6ToddN/a
Folder5EthanN/a
1 ACCEPTED SOLUTION
AlB
Super User
Super User

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"

 

SU18_powerbi_badge

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.

 

View solution in original post

1 REPLY 1
AlB
Super User
Super User

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"

 

SU18_powerbi_badge

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.