Searching number from one column into another column from a different table

[gusdcruz]

I have two tables that are not connected. Table 1 comes from a database and Table 2 comes from a SharePoint list.

Here is the first table

Table 1.

Ingredient Name	Ingredient Code	Location
Vitamin A (bag)	266	California
Vitamin A (tote)	267	Minnesota

Table 2.

Title	Code	How to use it?
VitaminA	266, 267	Feed accordingly to the user requirement

My goal is to integrate "How to use it?" column into table 1, so the user can search for the ingredient, and learn for to use the ingridient, Table 3 below shows how I would like to have it at the end

Table 3.

Ingredient Name	Ingredient Code	Location	How to use it?
Vitamin A (bag)	266	California	Feed accordingly to the user requirement
Vitamin A (tote)	267	Minnesota	Feed accordingly to the user requirement

Could someone please help me make this happen?

thank you

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.
https://1drv.ms/u/s!AiUZ0Ws7G26Rh0gJB_vsJpuAYO2T?e=VeqO4S

[Ashish_Mathur]

Hi,

You may split the Code column of Table2 by rows (in the Query Editor) and then merge the two tables.

[gusdcruz]

thank you for your time and approach. I actually used your suggestion on another problem that I was having 😉

[Anonymous]

Hi @gusdcruz ,

Please do the following for Table 2.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("FYsxCoAwDAC/EjJ3cqijiCA+QFxKB7FBA9piTBF/bzvdwXHO4cx6EhocUqiY0guaID8ErB1643BhXS+OfamNtQYa2xYdiQKs25YkcNzPr156UD0FhO7MQhdFRe9/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Title", type text}, {"Code", type text}, {"How to use it?", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Code", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Code.1", "Code.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Code.1", Int64.Type}, {"Code.2", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"Title", "How to use it?"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "code"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"How to use it?", "How to use"}})
in
    #"Renamed Columns1"

Then create a measure.

Measure = CALCULATE(MAX(Table2[How to use]),FILTER(ALL(Table2),Table2[code]=SELECTEDVALUE(Table1[Ingredient Code])))

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gusdcruz]

thank you for your time and suggestion