Power query comparing product profit across the years

Question

I have a dataset which having Company, Product, Profit and Year. Each year the company will sell few products and obtain the profits. Its not necessary the company should sale the same production in the next year. They may be left out previous products and add new few productions. I just want to apple to apple comparison of two year products like below.

My dataset is,

Company Product Profit  Year
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   20     2020
Y       Noodles  30     2020
Y       Shampoo  25     2021
Y       Switch   40     2021
Y       Soap     60     2022
Y       Shampoo  34     2022

I wanted to transform the table compared with the previous year's product for each respective year of every company like below,

Look at the above, the company didn't sell any soap in the year 2021. I just want to consider it as 0 or null and wanted to transform the table like below. Is there any way to do this in the Power query?

score 3 · Accepted Answer · answered Jun 07 '22 at 01:22

Here's one method.

I don't have time this evening to do a detailed explanation, but if you read the M Code and comments; and also follow along the Applied Steps, you should be able to figure it out.

Basics

Create separate tables for each year
Join pairs of tables using Product as the Key
Expand the paired tables
Fill in the blank years and products
Insert a blank row after each table pair

M Code

let

//Read in Data
//Change next two lines to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Company", type text}, {"Product", type text}, {"Profit", Int64.Type}, {"Year", Int64.Type}}),

//create yearly table pairs
    years = List.Sort(List.Distinct(#"Changed Type"[Year])),
    tables = List.Generate(
        ()=>[t=Table.ExpandTableColumn(
                Table.NestedJoin(
                    Table.SelectRows(#"Changed Type", (ft)=>ft[Year]=years{0}), "Product",
                    Table.SelectRows(#"Changed Type", (ft)=>ft[Year]=years{1}), "Product",
                    "Joined", JoinKind.FullOuter),"Joined",
                     {"Company","Product","Profit","Year"},{"Company1","Product1","Profit1","Year1"}),               
                idx=0],

            each [idx] < List.Count(years)-1,
            each [t=Table.ExpandTableColumn(
                Table.NestedJoin(
                    Table.SelectRows(#"Changed Type", (ft)=>ft[Year]=years{[idx]+1}),"Product",
                    Table.SelectRows(#"Changed Type", (ft)=>ft[Year]=years{[idx]+2}), "Product",
                    "Joined", JoinKind.FullOuter),"Joined",
                    {"Company","Product","Profit","Year"},{"Company1","Product1","Profit1","Year1"}),               
                idx=[idx]+1],
            each Table.InsertRows(            
                        Table.FillDown(Table.FillUp([t],{"Year", "Year1"}),{"Year","Year1"}),
                    Table.RowCount([t]),
                    {[Company="", Product="", Profit=null, Year=null, Company1=null, Product1="", Profit1=null, Year1=null]})
    ),
    #"Converted to Table" = Table.FromList(tables, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", 
        {"Company", "Product", "Profit", "Year", "Company1", "Product1", "Profit1", "Year1"}),

    //Fill in the blank company and products
    #"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 then r[Product1] else _},
                {"Product1", each if _ = null then r[Product] else _}

             } ))),
    #"Removed Columns" = Table.RemoveColumns(#"Fill in blanks",{"Company1"}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Removed Columns",1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Bottom Rows",{{"Company", type text}, {"Product", type text}, {"Profit", Int64.Type}, {"Year", Int64.Type}, {"Product1", type text}, {"Profit1", Int64.Type}, {"Year1", Int64.Type}})
in
    #"Changed Type1"

Will it work for more years? like from 2015 to 2022? and few companies has the years starting from 2017 to 2022 — Smith Dwayne, Jun 07 '22 at 03:12
@SmithDwayne Also, since you did not provide examples of data and desired output for the conditions you are now positing, you may need to edit the query to obtain whatever your desired output might be. Furthermore, if all years are not included in your data, you may want to change the `years` list to include all years from earliest to latest. — Ron Rosenfeld, Jun 07 '22 at 10:48
for the question above, I am marking this answer as correct. But I will edit the question to add further details. or Shall I make a separate question for that? — Smith Dwayne, Jun 07 '22 at 12:30
@SmithDwayne I'd suggest a separate question if this answer will not suffice for your more realistic data set. — Ron Rosenfeld, Jun 07 '22 at 12:40
I tested with multiple companies. But it's not grouped as Company and product. Instead, its uses only product — Smith Dwayne, Jun 07 '22 at 13:03
added a new question here. could you please take a look at it? https://stackoverflow.com/questions/72532319/excel-power-query-compare-companies-product-profit-of-every-adjacent-years — Smith Dwayne, Jun 07 '22 at 13:45
Also I added a comment to that question. It would be helpful what is am getting wrong with this solution. — Smith Dwayne, Jun 07 '22 at 14:08
I am seeing the duplicate rows added on the List.Generate function. But I don't know how to avoid it.. the duplicates added depends on the count of the companies. — Smith Dwayne, Jun 07 '22 at 14:52

Power query comparing product profit across the years

1 Answers1

Linked