Query Editor - New Column to look for values in another table column range.

[AliRade]

Hi Community,

I’m trying to add a new column in Power Query Editor which basically looks for a value in another table within a specific range set in that column. Example (Sample Data only):

Table A (Source Table)

Item	Section	Detail
Colour	X1	Train (Light Red)
Colour	X2	Train (Light Blue)
Colour	X3	Train (Light Green)
Size	X1	Size 10
Size	X2	Size 11
Size	X3	Size 12
Size	X4	Size 13
Weight	X1	Less than 10kg
Weight	X2	10kg-20kg
Weight	X3	Greater than 20kg

Note the Section values are repeated across different ‘Items’.

Table B (Custom Table)

Item Number	Section	Detail (Size) (NEW COLUMN)
10	X1	Size 10
11	X2	Size 11
12	X1	Size 10
13	X4	Size 13
14	X3	Size 12
15	X2	Size 11

Want to look up the ‘Section’ values From Table B in Table A where TableA.Item is ‘Size’. The Green font is what I’m trying to get the new column to output in Power Query Editor.

Similarly, if want to add additional column in Table B that looks up 'Section' value from Table B and return the 'Weight' Item from Table A. (i.e X2 from Table B would show value 10kg-20kg from Table A).

Any help on setting up a formula for this in Power Query Editor will be much appreciated.

[Anonymous · Accepted solution]

tabA:

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PyS8tUtJRijAEEiFFiZl5Cho+mekZJQpBqSmaSrE6yGqM0NU45ZSmoisyRlfkXpSamgdRFZxZlQqzDMRWMDRAETeCixuiiBvDxY1QxE3g4sZg8fBUkI0wG3xSi4sVSjIS84DWZKejKQBZBRLWNcKUA1kHdHViSWoRRD9ETSwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Section = _t, Detail = _t]),
    #"Raggruppate righe" = Table.Group(Origine, {"Item"}, {{"Value", each Record.FromTable(Table.RenameColumns(_[[Section],[Detail]],{{"Section", "Name"},{"Detail","Value"}}))}}),
    #"Rinominate colonne" = Record.FromTable(Table.RenameColumns(#"Raggruppate righe",{{"Item", "Name"}}))
in
    #"Rinominate colonne"

tabB:

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lGKMFSK1QGyDUFsIwjbCEncGMQ2gbBNQGxjCNsUqj4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Number" = _t, Section = _t]),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(Origine, "size", each Record.FieldOrDefault(tabA[Size],[Section],"nok"))
in
    #"Aggiunta colonna personalizzata"

just change the field from Size to Colour to get:

or

[AliRade]

Thanks Rocco. This works as intended. I'm getting the values as required in the new column in Table B.

This has been done by Grouping the data in TableA. Wondering if there is a way without modyfying Table A at all...?

[Anonymous]

try in this way , instead of the tabB query. I have not tested (there may be some syntax error) .

table A unmodified and this query should do everything in it.

let
    Origine = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lGKMFSK1QGyDUFsIwjbCEncGMQ2gbBNQGxjCNsUqj4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Item Number" = _t, Section = _t]),
    #"Raggruppate righe" = Table.Group(tabA, {"Item"}, {{"Value", each Record.FromTable(Table.RenameColumns(_[[Section],[Detail]],{{"Section", "Name"},{"Detail","Value"}}))}}),
    dict = Record.FromTable(Table.RenameColumns(#"Raggruppate righe",{{"Item", "Name"}})),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(Origine, "size", each Record.FieldOrDefault(dict[Size],[Section],"nok"))
in
    #"Aggiunta colonna personalizzata"

[Anonymous]

Of course it can be done without changing the structure of table A. For example by making use of the Table.SelectRows function. But I preferred (it's just a matter of taste) to create a kind of dictionary. In any case, if you need to have table A as originally, before applying the transformations in the dictionary, make a copy and call it tabAcopy or something else and make the transformations on this and keep tabA as you wish.

[AliRade]

Thanks. The Dicitionary method sounds like a good concept 👍

Like the idea of creating in dictinoary in Table B and then referring back to "Source" table (the second soultion you provided). It worked well.

Yes, thats what I had in mind in regards to keeping the Table A as is by making a copy of it and transforming the data on that copied table 🙂 It just that the table list would grow!

Thanks a lot for your help and tips Rocco. Much Appreciated 🙂