Calculate column value based on reference table in another table

[FJ2017]

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 (A2 &B2) and if the 2 conditions are met from the reference table it will return the value from column 3

=MAP(A2,B2,LAMBDA(x,y,LET(t,FILTER(Sheet2!$B$2:$C$35,Sheet2!$A$2:$A$35=x),XLOOKUP(y,INDEX(t,,1),INDEX(t,,2),"",-1))))

Greatful for any support on this.

[dufoq3]

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

[FJ2017]

Dear Manuel

Thanks for offering your help on this.  I attach som example data to explain the expected function.

Does this help ?

Regards

[v-xinruzhu-msft · Accepted solution]

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.

[FJ2017]

Thanks @v-xinruzhu-msft , this worked perfect in it's most simple way without extensive coding.  Much appreciated and have a nice day.

[ManuelBolz]

Hello @FJ2017,

If my post helped you, please give me a 👍kudos and mark this post with Accept as Solution.

The first code snippet is a rebuild from your Master Lookup reference table:

let
    //Replace the first Step with your Maser lookup refrence table
    //Source = YourMaserLookupRefrenceTable
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcstJLFHSUTIAYkMgVorVgYuZGoBEjdBEDQ3AwsYw4bDMnJxEdBNggmhGwITRzQjJL8/LyC8tTkU3B1nCyADJsFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, #"Size sqm" = _t, #"Size category" = _t, #"Size Span" = _t]),

    Type = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Size sqm", Int64.Type}, {"Size category", Int64.Type}, {"Size Span", type text}}),
    Rename = Table.RenameColumns(Type,{{"Size sqm", "Size"}})
in
    Rename

The second code snippet is a rebuild from your Output Data Table:

let
    //Replace the first Step with your Output Data Table
    //Source = YourOutputDataTable
    Source = 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]),
    Type = Table.TransformColumnTypes(Source,{{"Property Type", type text}, {"Size sqm", Int64.Type}}),
    RenameColumns = Table.RenameColumns(Type, {{"Property Type", "Type"}, {"Size sqm", "Size"}}),
    
    //Replace this Step with the correct name from your Master refrence lookup table
    //LookupSource = YourMasterRefrenceLookupTable
    LookupSource = MasterLookup,
    LookupType = Table.TransformColumnTypes(LookupSource, {{"Type", type text}, {"Size", Int64.Type}, {"Size category", Int64.Type}}),

    GetSizeCategory = (propertyType as text, size as number) as nullable number =>
        let
            FilteredTable = Table.SelectRows(LookupType, each [Type] = propertyType),
            SortedTable = Table.Sort(FilteredTable, {{"Size", Order.Descending}}),
            Match = Table.SelectRows(SortedTable, each [Size] <= size),
            Result = if Table.IsEmpty(Match) then null else Record.Field(Match{0}, "Size category")
        in
            Result,

    AddSizeCategory = Table.AddColumn(RenameColumns, "Size category", each GetSizeCategory([Type], [Size]), Int64.Type)
in
    AddSizeCategory

Best regards from Germany
Manuel Bolz

🟦Follow me on LinkedIn
🟨How to Get Your Question Answered Quickly
🟩Fabric Community Conference
🟪My Solutions on Github

[ManuelBolz]

Hello @FJ2017,

without a piece of data it is very difficult to help you. But basically I would say that it can be implemented with Power Query.

Best regards from Germany
Manuel Bolz

🤝Follow me on LinkedIn