Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
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
Hi @Anonymous,
If I understand you correctly, you need the button
in the right-top corner of the column [header]
Cheers,
John