Power Query - "MinIF" type function.

[Vaslo]

Hello -

I have a data dump where i get information on customer pricing. In some cases (but of course not all) I have tiered pricing where the customer gets a better price if they buy more. I would like to use the base price when not tiered, and then use the lowest price when tiered. I can use an if then to create one column to grab a "Base Price" when there is one, but I have a hard time in Power Query (and excel frankly) grabbing the min from a group in a column. So for example the first product 1234D: I'd look to do something like:

For Product 1234D and Customer 6555, when Break_Price is minimum, use that lowest price in the price column

So overall the logic is
When you have a Base Price, use that
When you have a Tiered Price (Break Price) use the Price where Price is Minimum.

As you can see its complicated by the fact that there are groups of minimum prices, so 1234A would use a totally different minimum price. I could do with an Array in regular Excel formulas but its heavy duty and I'd like to do in Power Query since I can just drop the new price list each month.

Can someone give advice on how to attack the minimum piece in Power Query?

https://www.dropbox.com/s/hle5rp0ctuil3se/Price Exercise.xlsx?dl=0

Thanks

 

[ImkeF]

Hi Vaslo,
this should do:

let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
GroupMin = Table.Group(Source, {"CUSTOMER_NUMBER", "SECONDARY_PRICELIST_ITEM_NO"}, {{"MinBreak", each List.Min([BREAK_PRICE]), type number}}),
MergeSourceWithGroupMin = Table.NestedJoin(Source,{"CUSTOMER_NUMBER", "SECONDARY_PRICELIST_ITEM_NO"},GroupMin,{"CUSTOMER_NUMBER", "SECONDARY_PRICELIST_ITEM_NO"},"NewColumn",JoinKind.Inner),
ExpandGroupMin = Table.ExpandTableColumn(MergeSourceWithGroupMin, "NewColumn", {"MinBreak"}, {"MinBreak"}),
ChooseWhich = Table.AddColumn(ExpandGroupMin, "NewPrice", each if [MinBreak] is null then [BASE_PRICE] else [MinBreak])
in
ChooseWhich

BR, Imke

 

[Vaslo]

Hi Vaslo,
this should do:

let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
GroupMin = Table.Group(Source, {"CUSTOMER_NUMBER", "SECONDARY_PRICELIST_ITEM_NO"}, {{"MinBreak", each List.Min([BREAK_PRICE]), type number}}),
MergeSourceWithGroupMin = Table.NestedJoin(Source,{"CUSTOMER_NUMBER", "SECONDARY_PRICELIST_ITEM_NO"},GroupMin,{"CUSTOMER_NUMBER", "SECONDARY_PRICELIST_ITEM_NO"},"NewColumn",JoinKind.Inner),
ExpandGroupMin = Table.ExpandTableColumn(MergeSourceWithGroupMin, "NewColumn", {"MinBreak"}, {"MinBreak"}),
ChooseWhich = Table.AddColumn(ExpandGroupMin, "NewPrice", each if [MinBreak] is null then [BASE_PRICE] else [MinBreak])
in
ChooseWhich

BR, Imke

This worked perfectly. I had a few other moves that I didn't share (promoted columns, etc.) but was able to easily change the code you provided to get it to add the extra column to the large spreadsheet. I can't thank you enough for your help.

 

[ImkeF]

Pleasure - great to see how PQ is making its way into the monthly routines.

Stay queryious

 

[Vaslo]

Pleasure - great to see how PQ is making its way into the monthly routines.

Stay queryious

I am singlehandedly pushing it in everything I can! If you don't mind me asking, how did you get so good with the M language? Not many resources out there to learn, and only starting to learn code in the last year or so has put me behind...

 

[ImkeF]

Chris Webb’s resources come first: book & blog

Other very good blogs are from Ken Puls and Miguel Escobar (very much looking forward their PQ book – their courses also look quite good, although I haven't had the chance yet to participate)

Very good reads also at Matt Masons blog, who unfortunatly become really quite this year.

You should also follow Gil Raviv, who has some very creative concepts to share: http://blogs.technet.com/b/gilraviv/

This site is always on: https://support.office.com/en-sg/ar...tegories-125024ec-873c-47b9-bdfd-b437f8716819

Apart from that: practice & passion

Just query on!