Transforming price list from one format to another with Power Query

LearningInProgress · January 8, 2023, 3:24am

Good day all,

First time posting a question here. We are in the middle of migrating from Sage ERP 300 to Sage X3 and we are looking at migrating the price lists to the new format.

Price List.xlsx (19.8 KB)

I have attached an excel file with the format of both systems. Sage 300 has a lot of information using the numerous fields on a single line in a table, whereas X3 needs one line per rule.

The rules are as follows for Sage 300

• The fields: CURRENCY, PRICELIST, ITEMNO, DESC, UOM are self-explanatory
• The base price for the unit of measure defined in the UOM field is in the DBASEPRICE field.
• The rest of the fields define volume pricing information for items based on the volumes purchased in the PRICEQTY[X] fields and the price in the respective AMOUNTLVL[X] field.
• As an example, if a customer purchases 1-29 boxes of Vanilla Ice Cream Bars, the price will be the base price of $30. If they purchase 30 – 149 boxes of the same product, they will purchase it at $29.50 per box. If they purchase 150 – 599 boxes, it will be $29 per box. This will continue until the customer purchases anything higher than 6000 boxes, which will be $27.50 per box. The Chocolate Ice cream bar works similarly except that the price level caps off at $30.50 per box for any quantity over 600 boxes. Since the Chocolate Chip Ice Cream Bar does not have any quantity and price level information, the base price of $35 per box will be used no matter what quantity is purchased.
I hope that the 2 tables clearly depict this. The problem is that I am not sure how to approach this problem using power query. I know that I need to unpivot all columns except the CURRENCY, PRICELIST, ITEMNO, DESC, UOM columns.

Can anyone please share a solution and an explanation of the solution to this problem? I will be extremely grateful for any assistance.

Thanks in advance.

AntrikshSharma · January 8, 2023, 9:24am

@LearningInProgress
Price List.xlsx (27.2 KB)

let
    Source = Table,
    Unpivot = 
        Table.UnpivotOtherColumns (
            Source,
            { "CURRENCY", "PRICELIST", "ITEMNO", "DESC", "UOM" },
            "FieldName",
            "Value"
        ),
    Group = Table.Group (
        Unpivot,
        { "CURRENCY", "PRICELIST", "ITEMNO", "DESC", "UOM" },
        {
            {
                "Transformation",
                ( CurrentGroup ) =>
                    let
                        PriceRows = 
                            Table.SelectRows (
                                CurrentGroup,
                                each Text.StartsWith ( [FieldName], "PRICE" )
                            )[Value],
                        AmountRows = 
                            Table.SelectRows (
                                CurrentGroup,
                                each (
                                    Text.StartsWith ( [FieldName], "DBASE" )
                                        or Text.StartsWith ( [FieldName], "AMOU" )
                                )
                                    and [Value] <> 0
                            )[Value],
                        ShiftValuesBy1 = 
                            List.Select (
                                List.Transform ( PriceRows, each _ - 1 ),
                                each _ <> - 1
                            ) & { 999999 },
                        ToColumns = 
                            Table.FromColumns (
                                { { 0 } & PriceRows } & 
                                    { ShiftValuesBy1 } & 
                                    { AmountRows },
                                type table [
                                    Minimum = Int64.Type,
                                    Maximum = Int64.Type,
                                    Price = Currency.Type
                                ]
                            ),
                        RemoveNulls = Table.SelectRows ( ToColumns, each [Maximum] <> null )
                    in
                        RemoveNulls,
                type table [ Minimum = Int64.Type, Maximum = Int64.Type, Price = Currency.Type ]
            }
        }
    ),
    Transformation = 
        Table.ExpandTableColumn (
            Group,
            "Transformation",
            { "Minimum", "Maximum", "Price" },
            { "Minimum", "Maximum", "Price" }
        )
in
    Transformation

LearningInProgress · January 8, 2023, 9:42pm

Hi @AntrikshSharma. Your solution worked perfectly! Thank you so much. I understand the group by, but I am trying to wrap my head around the steps in the transformation steps of the query. Any resource that you can point me to broaden my understanding? Thank you so much for your speedy response. If I mark your response as solution, can I post follow up questions?

Thanks.

AntrikshSharma · January 9, 2023, 8:10am

@LearningInProgress

BrianJ · January 9, 2023, 2:56pm

@LearningInProgress ,

Here’s a resource list of my favorite YouTube resources for learning M coding that you might find helpful:

Brian

LearningInProgress · January 10, 2023, 12:58pm

Thanks @AntrikshSharma. I have the code printed out and still trying to wrap my head around the steps. Generally, I get the idea and the diagram that you produced definitely help. After taking a second look at the code and the steps in the query, I didn’t understand the Table.Group as I thought I did. Right now, I am looking at examples of advanced grouping on youtube.

Hi @BrianJ . Thank you for pointing out your M and power query resources. There is definitely a LOT to learn and I am barely scratching the surface. Thank you so much and the entire Enterprise DNA team for all your hard work.

Cheers.