Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
I am a newbie to the powerBI tool.
I would like to seek community help to get some guidance on how to achieve the Requirement on power BI power query.
I have a table that has a product column that either has 1 product or multiple products separated by ';".
example
Product |
Pappaya green;Apple(Kashmir) |
Pappaya green;Kiwi (Udan (Stand Alone)) |
Pappaya green;Jack;Orange |
Pappaya green;Pomo (Seedless (Stand Alone)) |
Banana (Red) |
And I have a product list mentioned below
Prod List |
Jack |
Apple(Kashmir) |
Dates |
Cashew |
Pappaya green |
Kiwi (Udan (Stand Alone)) |
Banana (Red) |
Pomo (Seedless (Stand Alone)) |
lemon |
melon |
Grape |
cane |
Orange |
Requirement:
Now I want Table 1's first row compare with Table 2 and whichever is the word that matches first from Table 2, it returns that value in a newly created column in Table 1.
For the above example, the expected results in Table 1's new column should be
Mapped Product list |
Apple(Kashmir) |
Pappaya green |
Jack |
Pappaya green |
Banana (Red) |
Guidance to resolve this puzzle would be greatly appreciated as this can become a game-changer to my project.
I have seen @AlexisOlson helping a lookup issue(not the same, but small similarities )
Solved: Lookup data from another table when text exists - Power Platform Community (microsoft.com)
Would be of great help if some help can be provided on my requirement.
Solved! Go to Solution.
I think we can do this more simply than @Vijay_A_Verma suggests.
Take the first element of the intersection of Table2[Prod List] and the products in the current row.
List.First(List.Intersect({Table2[Prod List], Text.Split([Product], ";")}))
Here's a fully self-contained variation of this query:
let
Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKEisTFRIL0pNzbN2LCjISdXwTizOyM0s0lSK1UFX4J1ZnqmgEZqSmKegEVySmJei4JiTn5eqiU2tV2JytrV/UWJeeioW2YD83HygGampKTmpxcXYTHNKzANCBY2g1BSgQCwA", BinaryEncoding.Base64), Compression.Deflate)), type table [Product = (type text)]),
Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcxBCsIwEIXhq4SuUvASVUGwC4viqnTxaB61mExCUije3pC4lNl8/MzMODZXzO9mOoxNF4Kl7pFebo1tSWdsTEWnnLkXDggBH6glklJKv+6r0k8DUfqxQYzqrBe29ckRkkfpO00Ng3c+L5LGMqV/J5bO19eO9qdLRGDRDKm4RciSOX0B", BinaryEncoding.Base64), Compression.Deflate)), type table [#"Prod List" = (type text)]),
ProdList = List.Buffer(Table2[Prod List]),
#"Added Custom" = Table.AddColumn(Table1, "Mapped Product List", each
let
CurrRowProds = Text.Split([Product], ";"),
SharedProds = List.Intersect({ProdList, CurrRowProds})
in
List.First(SharedProds),
type text
)
in
#"Added Custom"
I think we can do this more simply than @Vijay_A_Verma suggests.
Take the first element of the intersection of Table2[Prod List] and the products in the current row.
List.First(List.Intersect({Table2[Prod List], Text.Split([Product], ";")}))
Here's a fully self-contained variation of this query:
let
Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKEisTFRIL0pNzbN2LCjISdXwTizOyM0s0lSK1UFX4J1ZnqmgEZqSmKegEVySmJei4JiTn5eqiU2tV2JytrV/UWJeeioW2YD83HygGampKTmpxcXYTHNKzANCBY2g1BSgQCwA", BinaryEncoding.Base64), Compression.Deflate)), type table [Product = (type text)]),
Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bcxBCsIwEIXhq4SuUvASVUGwC4viqnTxaB61mExCUije3pC4lNl8/MzMODZXzO9mOoxNF4Kl7pFebo1tSWdsTEWnnLkXDggBH6glklJKv+6r0k8DUfqxQYzqrBe29ckRkkfpO00Ng3c+L5LGMqV/J5bO19eO9qdLRGDRDKm4RciSOX0B", BinaryEncoding.Base64), Compression.Deflate)), type table [#"Prod List" = (type text)]),
ProdList = List.Buffer(Table2[Prod List]),
#"Added Custom" = Table.AddColumn(Table1, "Mapped Product List", each
let
CurrRowProds = Text.Split([Product], ";"),
SharedProds = List.Intersect({ProdList, CurrRowProds})
in
List.First(SharedProds),
type text
)
in
#"Added Custom"
Use this code in a custom column
Table2[Prod List]{List.Min(List.Transform(Text.Split([Product], ";"), (x)=> List.PositionOf(Table2[Prod List], x)))}
To handle error and show "None Found"
try Table2[Prod List]{List.Min(List.Transform(Text.Split([Product], ";"), (x)=> List.PositionOf(Table2[Prod List], x)))} otherwise "None Found"
Complete code in action
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKEisTFRIL0pNzbN2LCjISdXwTizOyM0s0lSK1UFX4J1ZnqmgEZqSmKegEVySmJei4JiTn5eqiU2tV2JytrV/UWJeeioW2YD83HygGampKTmpxcXYTHNKzANCBY2g1BSgQCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Table2[Prod List]{List.Min(List.Transform(Text.Split([Product], ";"), (x)=> List.PositionOf(Table2[Prod List], x)))})
in
#"Added Custom"