Question

我在努力寻找解决办法的同时,设法一劳永逸地 something住一些东西,但感到效率不高。

Problem Statement: I have 3 columns = [ Tool Name , Price , Country ] We group by Tool Name and max over the price. But we want to keep the country column, where for each tool name row with its corresponding max price, there will be the respective value of country which is from the same row as the max price.

Failed Method 1: Groupby and select all row, followed by expanding. Following from Power query group by 4 columns but keep the rest

然而,在扩大之后,它增加了一行,打败了履行集团义务的目的。

Current Solution: After groupby Tool Name only, I duplicated the original table and merge back the original table on tool name and price to get the correct country back into the groupby table. I then added this M code to take the first row of each match (since if I just expand, we will get back the same problem as above where rows are added)

= Table.AddColumn(#"Merged Queries", "Custom", each Table.First([merged]))

最后,我删除了合并的栏。

但是,这要求采取许多额外步骤,而其他软件,例如JMP,它保留了列车选择或假装,我们可以轻松地保留其他栏目。是否有更好的双管方法?

Answer 1

在专家组通过<条码>后,即:

Table.Group(#"Changed Type", {"Tool Name"}, {{"Rows", each _ }})

添加一条类似习俗:

= Table.AddColumn(#"Grouped Rows", "TopCountry", each Table.Last(Table.Sort([Rows], "Price")))

然后扩大该栏:

= Table.ExpandRecordColumn(#"Added Custom", "Country", {"Price", "Country"}, {"Price", "Country"})

最后,专家组将以前的“Rows”一栏 By倒。

Answer 2

在联邦调查局,你可以通过以下方式取得预期成果:

最初的表格有<代码>。工具名称,《价格》,国家。]
用工具名称栏将“组合”转换成组,并选定马克斯价格栏。
在小组运作后,将有一个新表格,编号为<代码>。工具名称:Max of Price ]。
将这一新表格重新贴在原来的表格上,将工具名称和价格栏的马克斯作为合并钥匙。这将使国家栏重新列入表。
扩大合并的表格,列入国家栏。
Finally, remove any unnecessary columns, such as the Max of Price column.

友情链接