excel 功效查询:连接所有行

bis0qfac  于 2022-12-30  发布在  其他
关注(0)|答案(2)|浏览(122)

我希望连接行而不是列。我可以通过透视和合并列来实现这一点,但当行发生变化时,该过程不可重复(列合并然后对值进行硬编码)。如何执行GroupBy函数,但连接而不是sum/count/etc?
注:值得注意的是,文本必须保持为文本类型。价格有时是275 pp或东西,我需要保持字母
下面是我正在尝试做的事情的视觉效果:

3okqufwl

3okqufwl1#

您只需要对Group by进行分组,并执行一些操作来添加引号和括号:

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Price", type text}, {"Group", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "quotPrice", each """" & [Price] & """"),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Group"}, {{"Grouped", each _, type table [Price=text, Group=number, quotPrice=text]}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Column([Grouped],"quotPrice")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"Custom", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Added Custom2" = Table.AddColumn(#"Extracted Values", "Text", each "[" & [Custom] & "]"),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Grouped", "Custom"})
in
    #"Removed Columns"

c6ubokkw

c6ubokkw2#

您要寻找的答案是将Table.Group()函数与Text.Combine()函数组合在一起。
这将为您工作:

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUjEyN1XSUTJUitUBcowNDBAcQyOQjBGEYwSWgXIMzZFkLMEyxkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Price = _t, Group = _t]),
  #"Grouped rows" = Table.Group(Source, {"Group"}, {{"Count", each "[""" & Text.Combine([Price], """, """) & """]", type text}})
in
  #"Grouped rows"

相关问题