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

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
Community Support

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 6
Super User

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

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

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

Community Support

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.

Regular Visitor

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

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

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

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors