Power query comparing product profit across the years-CodePudding

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?

CodePudding user response：

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"