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
chrisBLVD
Frequent Visitor

How to do a database "vlookup" or relationship

All,

 

I'm a little new to BI, so here is my issue:

 

- I have one dataset (Dataset 1) that includes the quantity of "crimes" by address. For example, Column A = Offense Type; Column B = Street Address

 

- I have another dataset (Dataset 2) that includes said addresses and the associated lat/longs with each. For example, Column A = Street Address; Column B = Latitude; Column C = Longitude.

 

What i'd like to do is align the latitude and longitudes from Dataset 2, to the correct addresses in Dataset 1. 

 

What's the best way to go about this? 

 

Best,

Chris

1 ACCEPTED SOLUTION
v-xuding-msft
Community Support
Community Support

Hi @chrisBLVD ,

 

You could try fuzzy merge as Jimmy801 suggested. Or you could create a custom column like this:

= Table.AddColumn(#"Changed Type", "Table 1",each (let currStrAdd = [Column B] in Table.SelectRows(#"Table 1",each [Column A]= currStrAdd)){0})
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k9LS80rTlUIqSxIVdJRCi4pSk0tUXBMSSlKLS5Wio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}}),
    Custom2 = Table.AddColumn(#"Changed Type", "Table 1",each (let currStrAdd = [Column B] in Table.SelectRows(#"Table 1",each [Column A]= currStrAdd)){0}),
    #"Expanded Table 1" = Table.ExpandRecordColumn(Custom2, "Table 1", {"Column B", "Column C"}, {"Table 1.Column B", "Table 1.Column C"})
in
    #"Expanded Table 1"
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-xuding-msft
Community Support
Community Support

Hi @chrisBLVD ,

 

You could try fuzzy merge as Jimmy801 suggested. Or you could create a custom column like this:

= Table.AddColumn(#"Changed Type", "Table 1",each (let currStrAdd = [Column B] in Table.SelectRows(#"Table 1",each [Column A]= currStrAdd)){0})
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k9LS80rTlUIqSxIVdJRCi4pSk0tUXBMSSlKLS5Wio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t, #"Column B" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}, {"Column B", type text}}),
    Custom2 = Table.AddColumn(#"Changed Type", "Table 1",each (let currStrAdd = [Column B] in Table.SelectRows(#"Table 1",each [Column A]= currStrAdd)){0}),
    #"Expanded Table 1" = Table.ExpandRecordColumn(Custom2, "Table 1", {"Column B", "Column C"}, {"Table 1.Column B", "Table 1.Column C"})
in
    #"Expanded Table 1"
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Jimmy801
Community Champion
Community Champion

Hello @chrisBLVD 

 

to use Table.NestedJoin, Table.Join or the fuzzy-version. I don't know your data quality, but addresses can always be written in a little different way, so the fuzzy-version may come in handy

 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

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.

Top Solution Authors
Top Kudoed Authors