Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
FJ2017
Regular Visitor

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.

1 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

vxinruzhumsft_0-1718173601859.png

 

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.

View solution in original post

6 REPLIES 6
dufoq3
Super User
Super User

Hi @FJ2017, different Power Query approach here:

 

Result

dufoq3_0-1718175363340.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

FJ2017
Regular Visitor

Dear Manuel

 

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

Does this help ?

 

Regards

Screenshot 2024-06-10 091949.png

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

vxinruzhumsft_0-1718173601859.png

 

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.

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

ManuelBolz
Responsive Resident
Responsive Resident

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
Responsive Resident
Responsive Resident

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors