Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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"
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"
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