PowerQuery formula to check if COG is in bouding Box

Question

I am trying to define in which box the cog of the object is. For example i have a item with COG (1.2, 1.5, 0.3) (x, y, z) and a list of boxes:

Number of Box	Z low	Z high	Y left	Y right	X front	X aft
1	0	3	-5	0	5	0
2	0	3	0	5	5	0

In this example the item is in box 2. My solution now is with a Sumifs, Checking for each border if the COG is lower/higher then the value of the border, which works as there are no overlaps of the boxes.

Most of the data is loaded with powerquery but I cannot make this sumifs statement work in powerquery, I have to load the data to excel and add the sumifs statements, which will not work if I want to load it in Power BI. Otherwise I have to open excel, refresh there and let the calculation run, save, open PBI and refresh there as well.

Is there an option to make this statement with sumifs or other solution only using Power BI?

Answer 1

You can just compare the x,y,z parameters and output a list of the box numbers that pass the test.

eg: (as a function)

(x as number, y as number, z as number) =>
let
    Box = Table.FromRecords({
        [Box Number=1, Z Low=0, Z High=3,Y Left=-5,Y Right=0,X Front=5,X Aft=0],
        [Box Number=2,Z Low = 0, Z High=3, Y Left=0, Y Right=5,X Front=5,X Aft=0]
    }),

    typeIt = Table.TransformColumnTypes(
            Box, List.Transform(Table.ColumnNames(Box), each {_,Int64.Type})
    ),
    inOut = Table.AddColumn(typeIt,"ckBox", each 
        x >= [X Aft] and x<= [X Front] and 
        y >= [Y Left] and y <= [Y Right] and 
        z >= [Z Low] and z <= [Z High]
    ),
    boxNum = Table.SelectRows(inOut,each [ckBox] = true)
in 
    boxNum[Box Number]