Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

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)

 

lookup1.jpg

 

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

3 REPLIES 3
Anonymous
Not applicable

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'

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

jbwtp
Memorable Member
Memorable Member

Hi @Anonymous,

 

If I understand you correctly, you need the button

jbwtp_1-1676927761647.png

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

jbwtp_0-1676927730906.png

 

Cheers,

John

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors