Look for several codes of the same row in another table

[Spotto]

Hi everyone, I need some help. I have a table A with many codes separated by ( ; ) on the same row.
I would like to look up each code by line in another table B where the codes are individual and return a value

Ex.:

TABLE A

COD

1234;45;33

23;875;

23

-------------------------------

TABLE B

COD       VALUE

123          34,34

23            123,99

33            234,55

875          123,44

tks for help me

[AnkitKukreja]

Hi! @Spotto 

Please try the below code and paste in the advance editor of your blank query and your Table A would look like table B (see below). Let me know if it helps or if you need anything else.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNrE2MbU2NjYytrYwN7U2MlaKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [COD = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"COD", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "COD", Splitter.SplitTextByDelimiter(";", QuoteStyle.None), {"COD.1", "COD.2", "COD.3", "COD.4", "COD.5"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"COD.1", Int64.Type}, {"COD.2", Int64.Type}, {"COD.3", Int64.Type}, {"COD.4", Int64.Type}, {"COD.5", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {}, "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Cod"}})
in
#"Renamed Columns"

[BA_Pete · Accepted solution]

Hi @Spotto ,

In Power Query, add a new column in TableA like this:

Text.Split([COD], ";")

Expand this column to new rows and you will have single values to compare/merge to from TableB.

If you need a single row per transaction/dimension in tableA, then just group the table after your merge and sum your value field.

Pete