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

Question

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.

Does this answer your question? [Power query comparing product profit across the years](https://stackoverflow.com/questions/72521965/power-query-comparing-product-profit-across-the-years) — Solar Mike, Jun 07 '22 at 13:53
@SolarMike: No. its duplicating products. for example, if a number of companies are 'n', the n number of each product gets duplicated ... I tried to modify the query. But it's not working. Also, I just want to know if I get any alternate solutions. — Smith Dwayne, Jun 07 '22 at 14:03
Perhaps check this out and edit it to suit: https://stackoverflow.com/a/56016036/4961700 — Solar Mike, Jun 07 '22 at 14:12
[mre] [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) — philipxy, Jun 08 '22 at 02:04

Ron Rosenfeld · Accepted Answer · 2022-06-10T02:14:57.297

This can be accomplished by

Modify previous answer to related question to be used as a Function
Groupby Company, then run the function against each subtable

Edited to add extra year if there is only one in the data for a company
and to fill in blank year when no product sold

Custom Function
paste into blank query

//Name query "fnProcessTable"

(myTable as table)=>

let

//create yearly table pairs
    yearList = {List.Min(myTable[Year])..List.Max(myTable[Year])},

//test for no profits reported and just one year
    years = if List.Count(yearList) > 1 then yearList else 
        {yearList{0}-1..yearList{0}},

    tables = List.Generate(
        ()=>[t=Table.ExpandTableColumn(
                Table.NestedJoin(
                    Table.SelectRows(myTable, (ft)=>ft[Year]=years{0}), "Product",
                    Table.SelectRows(myTable, (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(myTable, (ft)=>ft[Year]=years{[idx]+1}),"Product",
                    Table.SelectRows(myTable, (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 and List.Count(yearList) > 1 then r[Product1] else _},
                {"Product1", each if _ = null then r[Product] else _},
                {"Year", each if _ = null then r[Year1]-1 else _}

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

Main Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company", type text}, {"Product", type text}, {"Profit", Int64.Type}, {"Year", Int64.Type}}),
    
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Company"}, {
        {"tbl", each fnProcessTable(_)}
        }),
    
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Company"}),
    #"Expanded tbl" = Table.ExpandTableColumn(#"Removed Columns", "tbl", 
        {"Company", "Product", "Profit", "Year", "Product1", "Profit1", "Year1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded tbl",{{"Company", type text}, {"Product", type text}, {"Profit", Int64.Type}, {"Year", Int64.Type}, {"Product1", type text}, {"Profit1", Int64.Type}, {"Year1", Int64.Type}}),
    
//irrelevant rows generated by companies that show no year over year profit to be removed
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Selector", each [Product]=null and [Product1]=null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Selector] = false)),
    #"Remove Selector Column" = Table.RemoveColumns(#"Filtered Rows",{"Selector"}),

//remove bottom row which is an extra null row
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Remove Selector Column",1)
in
    #"Removed Bottom Rows"

Source

Results

On the ExpandTableColumns, I am getting An error occurred in the ‘’ query. Expression.Error: The column 'Column1' of the table wasn't found. error. — Smith Dwayne, Jun 07 '22 at 17:37
Its due to few companies has only one year of profit report. the above error due to if a company has only one year of profit reports. Can we handle the case here? — Smith Dwayne, Jun 07 '22 at 17:51
I want to show the previous year as null. Like this `Z ... null ... null...2020 ... Noodles ... 25 ... 2021` — Smith Dwayne, Jun 08 '22 at 06:57
@SmithDwayne That's doable. Just need to edit the function a bit. Later. — Ron Rosenfeld, Jun 08 '22 at 12:33
@SmithDwayne Ok I've edited and I show two possible outcomes. See which you prefer. — Ron Rosenfeld, Jun 08 '22 at 21:54
[Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) — philipxy, Jun 09 '22 at 02:32
@philipxy The data is available in text form in the original question above. — Ron Rosenfeld, Jun 09 '22 at 10:17
@RonRosenfeld: the last row .. I need to have the previous year 2019 as well need to be shown. — Smith Dwayne, Jun 09 '22 at 16:52
@SmithDwayne You just need to add a line to the `#"Fill in blanks"` step referencing the `Year` and `Year1` columns. — Ron Rosenfeld, Jun 09 '22 at 17:42
@SmithDwayne I have edited the code to provide what I think you are describing, with the previous year now showing up. — Ron Rosenfeld, Jun 10 '22 at 02:15

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

1 Answers1

Linked