PowerQuery:如何连接分组值? [英] PowerQuery: How can I concatenate grouped values?
问题描述
如果我有下表(如下图所示),我如何编写一个分组的查询来连接分组的结果?
If I have the following table (shown in the image below), how can I write a grouped query that would concatenate the grouped results?
对于这个例子,我想通过 LetterColumn
分组,并连接 NumberColumn
For this example, I'd want to group by the LetterColumn
and concatenate the NumberColumn
所以希望的结果是:
推荐答案
如果您的表是Source,如果NumberColumn有数字类型,将工作:
If your table is Source, and if NumberColumn has the number type, then this will work:
= Table.Group(Source,{LetterColumn},{{Column,each Text.Combine变换(_ [NumberColumn],(x)=> Number.ToText(x)),,),键入text}})
Table.Group
通过操作执行一个组,它创建一个由LetterColumn中具有相同值的所有行组成的表。 _ [NumberColumn]
给出了这个新表中NumberColumn列中的值的列表。 List.Transform
部分将数字转换为文本值, Text.Combine
将这些数字连接在一起,并带有逗号分隔每个值。
Table.Group
does a group by operation, which creates a table made up of all of the rows with the same value in LetterColumn. _[NumberColumn]
gives a list of the values in the NumberColumn column in this new table. The List.Transform
part turns the numbers into text values, and Text.Combine
joins those numbers together, with a comma separating each value.
如果您还需要周围的引号,可以这样做:
If you need the surrounding quotes as well, you can do this:
= Table.Group(Source,{LetterColumn},{{Column,each& Text.Combine(List.Transform(_ [NumberColumn],(x)=& .ToText(x)),,)&,键入text}})
表示字符,并合并两个文本值。
""""
represents the " character, and & combines two text values.
这篇关于PowerQuery:如何连接分组值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!