Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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"