Excel-电力查询比较邻近的公司产品利润

发布于 2025-02-05 03:51:32 字数 1022 浏览 4 评论 0原文

我有一个具有公司,产品,利润和年份的数据集。每年,公司都会出售一些产品并获得利润。公司的开始年将因公司而异。我在下面还提供了我的数据集,除了所附的结果。我不知道该如何到达电源查询中的解决方案。

我的数据集,

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

“在这里输入图像描述”

我的预期结果是,

“在此处输入图像描述”

我只想比较相邻几年的每种产品的利润。

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

enter image description here

My expected result would be,

enter image description here

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

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

-黛色若梦 2025-02-12 03:51:33

这可以通过

编辑以增加额外的年份,如果公司的数据中只有一个
并填写空白的一年


粘贴到空白查询

//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"

主代码

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

结果

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
enter image description here

Results
enter image description here

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文