Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
I have 3 different tables/ sheets
1st one
First Name Last Name | Sales |
Manmohan Singh | 50 |
Rajnath Bendre | 25 |
Mohammad Abdul | 30 |
2nd one
Lastname Firstname | Spent |
Singh Manmohan | 25 |
Bendre Rajnath | 15 |
Abdul Mohammad | 10 |
3rd one
Firstname | Target |
Manmohan | 100 |
Rajnath | 80 |
Mohammad | 75 |
Result I need is
Name | Sales | Spent | Target |
Manmohan Singh | 50 | 25 | 100 |
Rajnath Bendre | 25 | 15 | 80 |
Mohammad Abdul | 30 | 10 | 75 |
Can you please help?
Solved! Go to Solution.
@balajideshpande I hope this helps you! Thank You!!
>Merge query with Fuzzylookup.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k3My83PSMxTCM7MS89Q0lEyNVCK1YlWCkrMykssyVBwSs1LKUoFihuZgsV9gYpzcxNTFByTUkpzgOLGQPWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name Last Name" = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"First Name Last Name", type text}, {"Sales", Int64.Type}}),
#"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"First Name Last Name"}, #"Table B", {"Lastname Firstname"}, "Table B", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true]),
#"Expanded Table B" = Table.ExpandTableColumn(#"Merged Queries", "Table B", {"Spent"}, {"Table B.Spent"}),
#"Merged Queries1" = Table.FuzzyNestedJoin(#"Expanded Table B", {"First Name Last Name"}, #"Table C", {"Firstname"}, "Table C", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.5]),
#"Expanded Table C" = Table.ExpandTableColumn(#"Merged Queries1", "Table C", {"Target"}, {"Table C.Target"})
in
#"Expanded Table C"
@balajideshpande I hope this helps you! Thank You!!
>Merge query with Fuzzylookup.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k3My83PSMxTCM7MS89Q0lEyNVCK1YlWCkrMykssyVBwSs1LKUoFihuZgsV9gYpzcxNTFByTUkpzgOLGQPWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"First Name Last Name" = _t, Sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"First Name Last Name", type text}, {"Sales", Int64.Type}}),
#"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"First Name Last Name"}, #"Table B", {"Lastname Firstname"}, "Table B", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true]),
#"Expanded Table B" = Table.ExpandTableColumn(#"Merged Queries", "Table B", {"Spent"}, {"Table B.Spent"}),
#"Merged Queries1" = Table.FuzzyNestedJoin(#"Expanded Table B", {"First Name Last Name"}, #"Table C", {"Firstname"}, "Table C", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true, Threshold=0.5]),
#"Expanded Table C" = Table.ExpandTableColumn(#"Merged Queries1", "Table C", {"Target"}, {"Table C.Target"})
in
#"Expanded Table C"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.