Creating new column in power query with multiple criteria

[naea07]

Hi, currently the information that in have in power query is the stock quantity that each branch (Branch A, B, C, D, E) has and the requested qty that is to be transfer from Branch A to other branches.

What I would like is to have a new column (the yellow highlighted ones) in this power query to show the stock (from branch A) that can be transfer to another branches (B, C, D, E), given that the order of the transfer will always be prioritize to B first then C then D then E.

(I could write this through normal formula in excel but this process will be repeated by many times for different dataset so i think doing in power query will be much more neat and flexible)

How can this be done? Thanks for the help in advance

Attachment 673651

[naea07]

Attached the excel file

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[naea07]

thank you, i just got it

[AliGW]

Sorry - do you mean you have solved the issue?

If you still need help, please explain the results you are looking for - I don't see the logic.

[naea07]

No it's not solved yet. When I reply I got it, i meant I got how to upload the excel file part.

The logic behind the number is that item X has 33 stock available for transfer. By going through the branch in the order of priority:
- Branch B has requested no stock so the stock of Branch A remains at 33
- Branch C has requested 31 stock so 31 will be transfer from A to C, leaving A with 2 stock
- Branch D has requested 8, but A has only 2 left so the stock to be transfer will be 2.

The stock that is to be transfer to each branch will be from Branch A only, and the order of the transfer will always be B, C, D then E.

Hope this makes it more clear. Thanks.

[AliGW]

Thanks. The incremental nature of what you want to achieve is above my pay grade in PQ - sorry. Hopefully Olly or another PQ wizard will see this.

[Olly]

You could use a query like:

let
    fnSourceStock = (ItemData, Item) =>
        let
            #"Filtered ItemData" = Table.SelectRows(ItemData, each ([Item number] = Item) and ([Branch] = "A")),
            #"Source Stock" = List.Sum(#"Filtered ItemData"[Current stock])
        in
            #"Source Stock",

    fnPriorDemand = (ItemData, Item, Branch) =>
        let
            #"Filtered ItemData" = Table.SelectRows(ItemData, each ([Item number] = Item) and ([Branch] <> "A") and ([Branch] < Branch)),
            #"Prior Demand" = List.Sum(#"Filtered ItemData"[To be transfer from Branch A])
        in
            #"Prior Demand",

    Source = Excel.CurrentWorkbook(){[Name="ItemData"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item number", type text}, {"Branch", type text}, {"Current stock", Int64.Type}, {"To be transfer from Branch A", Int64.Type}}),
    #"Added Source Stock" = Table.AddColumn(#"Changed Type", "Source Stock", each fnSourceStock(#"Changed Type", [Item number]), Int64.Type),
    #"Added Prior Demand" = Table.AddColumn(#"Added Source Stock", "Prior Demand", each fnPriorDemand(#"Changed Type", [Item number], [Branch]), Int64.Type),
    #"Added Available Qty" = Table.AddColumn(#"Added Prior Demand", "Available To Transfer", each List.Max ({0, [Source Stock] - [Prior Demand]}), Int64.Type),
    #"Added Transfer Qty" = Table.AddColumn(#"Added Available Qty", "Transfer From Source", each List.Min({[Available To Transfer], [To be transfer from Branch A]}), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Transfer Qty",{"Source Stock", "Prior Demand", "Available To Transfer"})

in
    #"Removed Columns"

However - I'd use PowerPivot instead.

First of all, I'd create a Branches table - as I'm sure your branch names won't always be in conveniently alphabetical order - so this table establishes the source branch, and the transfer priority of the remainder.

tables.png

I'd load both tables to the data model (using Power Query) and create a relationship on [Branch]:

relationship.png

Now you can add a calculated column to table ItemData:

TransferFromSource:=
VAR SourceStock = 
	CALCULATE ( 
		SUM ( ItemData[Current stock] ),
		ALLEXCEPT ( ItemData, ItemData[Item number] ),
		Branches[Priority] = "Source"
	)
VAR CurrentPriority = 
	RELATED ( Branches[Priority] )
VAR PriorDemand = 
	CALCULATE ( 
		SUM ( ItemData[To be transfer from Branch A] ),
		ALLEXCEPT ( ItemData, ItemData[Item number] ),
		FILTER ( 
			ALL ( Branches[Priority] ),
			Branches[Priority] <> "Source" && Branches[Priority] < CurrentPriority
		)
	)
VAR AvailableToTransfer = 
	MAX ( 0, SourceStock - PriorDemand )
RETURN
	MIN ( AvailableToTransfer, ItemData[To be transfer from Branch A] )

Gives your expected results:
pp_output.png

[Bo_Ry]

Another PQ

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Stock2A = Table.AddColumn(Source, "Stocks to be transfer from A",(x)=> 
List.Max({0,List.Min({List.Sum(Table.SelectRows(Source, each [#"Item number"] = x[#"Item number"]  and [Branch] =" A")[Current stock] )  - 
List.Sum(Table.SelectRows(Source, each [#"Item number"] = x[#"Item number"]  and [Branch] <x[Branch])[To be transfer from Branch A]),  
List.Sum(Table.SelectRows(Source, each [#"Item number"] = x[#"Item number"]  and [Branch] = x[Branch])[To be transfer from Branch A]) })}))
in
    Stock2A