How to solve the formula of SUMIFS()/XLOOKUP() in power query formula?

[mdshk]

I have a set of data in power query which consists of Column A to Column W. I am trying to develop a formula in power query editor. The formula is

=SUMIFS($T$2:$T$73,$D$2:$D$73,D2,$W$2:$W$73,W2)/XLOOKUP(W2,Sheet2!A:A,Sheet2!B:B)

To get the file, please click here.

In sheet1, of the file, X column, capacity utilization will be calculated and it will be done applying the formula in each cell of Column X. And I was trying to implement the formula using power query since the data set will be huge and using the formula will slow down the operation in the file.

Is it possible to develop the formula in power query?? FYI, I am using Office365. Thanks in advance.

[Vijay_A_Verma]

Use this query where Table2 is table in Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"SL", Int64.Type}, {"Vat RegNo", type any}, {"Invoice Date", type date}, {"Customer Name", type text}, {"Address", type text}, {"Product #(lf)Code", type any}, {"Product Name", type text}, {"Sales Qty", Int64.Type}, {"Unit Price", type number}, {"Value", Int64.Type}, {"SD", Int64.Type}, {" VAT", type number}, {"Total Price", type number}, {"Unit Name", type text}, {"Destination", type text}, {"Truct No", type text}, {"VAT No", Int64.Type}, {"Remarks", type any}, {"LCFT", type number}, {"Area", type text}, {"Transport", type text}, {"Veh No.", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Invoice Date", "Veh No."}, {{"Num", each List.Sum([LCFT]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Veh No."}, Table2, {"VEHICLE"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"HCFT"}, {"Den"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table1", "Capacity Utilization", each [Num]/[Den]),
    #"Merged Queries1" = Table.NestedJoin(#"Changed Type", {"Invoice Date", "Veh No."}, #"Added Custom", {"Invoice Date", "Veh No."}, "Added Custom", JoinKind.LeftOuter),
    #"Expanded Added Custom" = Table.ExpandTableColumn(#"Merged Queries1", "Added Custom", {"Capacity Utilization"}, {"Capacity Utilization"})
in
    #"Expanded Added Custom"

[mdshk]

Hi Vijay,

Thank you so much for the solution. But sorry to say, I cannot apply the formula to get the desired output. 

I have made the sheet1 as Table1 and Sheet2 as Table2 and both tables are as connection only. Then in the connection of Table1, I tried to add a custom column and applied the formula you shared (like in picture 1).

But unfortunately, in return, I am getting error message like below picture. 

Could you please help me to sort out the issue? Thank you so much for your support and time.

[Vijay_A_Verma · Accepted solution]

Download the work file from here - https://1drv.ms/x/s!Akd5y6ruJhvhuXsKVmWA7IB377sF?e=r10BAO 

Result tab has the result. 

[tleonardo]

Hello Vijay,

I have almost the same problem I need to solve in Power Query, but the xlookup is within the same table. This is the current formula I have in excel.

=(SUMIF([placementID],XLOOKUP(1,([CandidateReplaced]=[@Candidate])*([Client]=[@Client]),[placementID],0),[BillingCredit]))*([@BillingCredit]/SUMIF([placementID],[@placementID],[BillingCredit]))

Would you be able to provide a logic I can put in power query or you would need to see the file?

[dufoq3]

Hi @tleonardo, provide sample data in usable format and expected result based on sample data please.

[mdshk]

Hi Vijay, 

This is exactly what I was trying to establish. Thank you very much once again.