Forum Discussion

FJ2017's avatar
FJ2017
Regular Visitor
2 years ago
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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
FJ2017
Regular Visitor
2 years ago

Dear Manuel

 

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

Does this help ?

 

Regards

ManuelBolz's avatar
ManuelBolz
Responsive Resident
2 years ago

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