多年来比较产品利润的功率查询

Question

我有一个具有公司，产品，利润和年份的数据集。每年，公司都会出售几种产品并获得利润。该公司不必在明年出售相同的生产。它们可能被遗漏了以前的产品，并添加了新的几种产品。我只想将苹果与以下两年产品进行比较。

我的数据集是，

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

我想与每个公司的上一年的产品相比，在下面的每个公司的产品中，

​t在2021年出售任何肥皂。我只想将其视为0或NULL，并想改变下面的桌子。在电源查询中有什么方法可以做到这一点？

Answer 1

这是一种方法。

我今天晚上没有时间进行详细的解释，但是如果您阅读M代码和评论；并且还遵循应用步骤，您应该能够弄清楚它。

基础知识

• 每年
• 使用产品组合一对表，作为键
• 展开配对表
• 填充在空白年中，并
• 在每个表对后插入空白行

m代码

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"