Forum Discussion
Solved
Calculate column value based on reference table in another table
Gday all. I have been using this formula in excel but would like switch over to power BI and use it there instead. Basically it will check in a reference table to match the values from 2 columns ...
Hi,
Thanks for the solution ManuelBolz provided, and i want to offer some more infotmation for user to refet to.
hello FJ2017 , you can create a calculated column in data table.
Column = MAXX ( FILTER ( 'Lookup_table', [Siza(sq.m)] <= EARLIER ( 'Table'[Size(sq.m)] ) && [Type] = EARLIER ( 'Table'[Property Type] ) ), [Size Category] )Ouptut
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi FJ2017, different Power Query approach here:
Result
let
DataTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc07DkBAEIDhu2yt2HnuuoATiEYUCgnJhgJxfc+IRTlf/pkpS5OFejKJAWutqZJ7Vqv4nFFeQcrCZ1F0IdSbCHuOQD2TjwTAo6OYSBymB+XD0rfDPDb7sevb09TJxxB+QkCib0m6rVcr", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Property Type" = _t, #"Size sqm" = _t]),
LookupTable = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcstJLFHSUTIAYkOlWB24gKkBSMgIWcjQACxmDBYLy8zJSUTRCBNB1gkTQ9Eakl+el5FfWpyKoh1Z1MgAZkYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Size = _t, #"Size category" = _t]),
DataTableChangedType = Table.TransformColumnTypes(DataTable,{{"Property Type", type text}, {"Size sqm", Int64.Type}}),
LookupTableChangedType = Table.TransformColumnTypes(LookupTable,{{"Size", Int64.Type}, {"Size category", Int64.Type}}),
MergedQueries = Table.NestedJoin(DataTableChangedType, {"Property Type"}, LookupTableChangedType, {"Type"}, "LookupTableChangedType", JoinKind.LeftOuter),
Ad_SizeCategory = Table.AddColumn(MergedQueries, "Size Category", each Table.Last(Table.SelectRows([LookupTableChangedType], (x)=> [Size sqm] >= x[Size]))[Size category], Int64.Type),
RemovedColumns = Table.RemoveColumns(Ad_SizeCategory,{"LookupTableChangedType"})
in
RemovedColumns