Excel - Power query compare companies product profit of every adjacent years-CodePudding

I have a dataset that has Company, Product, Profit, and Year. Each year the company will sell a few products and obtain profits. The company's starting year will vary depending on the company. I provided my data set below also excepting the result attached. I don't know how to arrive the solution in the power query.

My data set,

X       Soap     20      2020
X       Shampoo  30      2020
X       Noodles  25      2021
X       Shampoo  40      2021
X       Coffee   60      2022
X       Shampoo  34      2022
Y       Coffee   25      2018
Y       Noodles  20      2018
Y       Coffee   30      2019
Y       Noodles  25      2019
Y       Coffee   20      2020
Y       Soap     30      2020
Y       Shampoo  25      2021
Y       Switch   40      2021
Y       Soap     60      2022
Y       Shampoo  34      2022

My expected result would be,

I just want to comparison over each product's profit across the adjacent years.

CodePudding user response：

This can be accomplished by

Modify

Results

Alternate #"Fill in blanks" code

...
    #"Fill in blanks" = Table.FromRecords(
        Table.TransformRows(#"Expanded Column1",
            (r)=> Record.TransformFields(r,{
                {"Company", each if _ = null then r[Company1] else _},
                {"Product", each if _ = null and List.Count(yearList) > 1 then r[Product1] else _},
                {"Product1", each if _ = null then r[Product] else _}

             } ))),
...

Alternate Results if preferred