Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Lookup within same table like vlookup

Hello,

 

I'm new to PQ and cannot figure out how to look up a value in vlookup style.

 

Somehow I managed to collect and merge data from different sources up to this point:

(Excel german interface)

 

 

So in the query I have line items, which have a column VariantTotal in this example it sums up to 60 - upper left red circle

In the upper right red circle you can see a table, the contents of this table shows price groups.

But instead of displaying 'table' in the column 'tblSujetsPriceGroups' I'd like to display the value ("Wert" lower right red circle) where VariationTotal is the next smaller number in the Attribute column.

So insted of table I want to see 4,55 in row 1

and in row 5 it must be 1,98 (the next price group starting at 100pcs).

 

Here's how the last step looks like in the editor where I combined this price groups table into the query:

 

#"Zusammengeführte Abfragen1" = Table.NestedJoin(#"Geänderter Typ2", {"tblPrintSujets.CalculationHelper"}, tblSujetPriceGroups, {"SujetPriceGroups"}, "tblSujetPriceGroups", JoinKind.LeftOuter)
in
    #"Zusammengeführte Abfragen1"

 

 

Thank's for helping

Tom

Need Help

3 Replies

  • jbwtp's avatar
    jbwtp
    Memorable Member
    3 years ago

    Hi Anonymous,

     

    If I understand you correctly, you need the button

    in the right-top corner of the column [header]

     

    Cheers,

    John

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi jbwtp 

     

    as you can see the contents of 'Table' on the bottom of the screenshot, this alone will not work, cause 'Table' contains more than one value. The result I want: give me the next smaller or similiar value from 'VariationTotal' which you find in 'Table'

    • jbwtp's avatar
      jbwtp
      Memorable Member
      3 years ago

      Hi Tom,

       

      Ok, this is what you want:

      let
    t1 = 
        let
            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjNVitWJVjKGUBYQyhxCGRpAaBOEaCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t]),
            #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}})
        in
            #"Changed Type",
    t2 = 
        let
            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRQ0lFyVIrViVYyAjGdwExjENMZzDQBMV3ATFMQ0xXMNAMx3cBMcxDTXSk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CompareTo = _t, ReturnThis = _t]),
            #"Changed Type" = Table.TransformColumnTypes(Source,{{"CompareTo", Int64.Type}, {"ReturnThis", type text}})
        in
            #"Changed Type",
    columnNames = [v = "Value", ct = "CompareTo", rt = "ReturnThis"],
    
    Combine = 
        Table.AddColumn(t1, "glued", each t2),
    Process = Table.ReplaceValue(Combine, each Record.Field(_, columnNames[v]), each List.Max, (x, y, z) => let f = z(List.Select(Table.Column(x, columnNames[ct]), each _ < y)), o = Table.SelectRows(x, each Record.Field(_, columnNames[ct]) = f) in Record.Field(o{0}, columnNames[rt]) ,{"glued"})
in Process

       

      You will need to add it to your code (in the Advanced Editor). Just point t1 and t2 to your tables and change the column names in the columnName record to you ones. Something like:

      let
    t1 = qryPrint1to3,
    t2 = tblSubjectPriceGroup,
    columnNames = [v = "variationTotal", ct = "Attribut", rt = "Wert"],
    
    Combine = ...

      It should work.

       

      Cheers,

      John