Calculate column value based on reference table in another table

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