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 (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.

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.

6 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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'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

    • Anonymous's avatar
      Anonymous
      Not applicable
      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

        Thanks Anonymous , this worked perfect in it's most simple way without extensive coding.  Much appreciated and have a nice day.