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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors