将列转换为单元格字符串Power Query [英] Convert column to cell string Power Query
问题描述
我需要将Power Query中一列的所有值放入一个用逗号分隔的1单元格字符串中,如下例所示:
I need to fit all the values of a column in Power Query into a 1-cell string separated by commas, as the example below:
为此,我需要以下代码:
To do this, I have the following piece of code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Transposed Table" = Table.Transpose(Source),
#"Merged Columns" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2", "Column3"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
#"KeepString" = #"Merged Columns"[Merged]{0}
in
#"KeepString"
此代码的问题在于,它假定总会有3列,但情况并非总是如此.如何将所有列(无论有多少列)合并为一个?
The problem with this code is that it assumes there will always be 3 columns, which is not always the case. How can I merge all columns (regardless of how many there are) into one?
推荐答案
您可以使用 List.Accumulate
:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
KeepString = List.Accumulate(Source[User], "", (state, current) => if state = "" then current else state & "," & current)
in
KeepString
您还可以使用 Table.ColumnNames
获取所有列名称的列表.您可以将其传递到 Table.CombineColumns
中,因此修改后的解决方案将是:
You can also use Table.ColumnNames
to get the list of all the column names. You can pass this into Table.CombineColumns
, so your modified solution would be:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Transposed Table" = Table.Transpose(Source),
#"Merged Columns" = Table.CombineColumns(#"Transposed Table", Table.ColumnNames(#"Transposed Table"),Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
#"KeepString" = #"Merged Columns"[Merged]{0}
in
#"KeepString"
这篇关于将列转换为单元格字符串Power Query的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!