提前感谢你的帮助!
你建议我如何从这个表中进行选择:
Equipment Desc | Ship To Name
------------------+----------
Small | Bob
Small | Dylan
Small | Joe
Small | Ethan
Small | Bob
Medium | Joe
Medium | Michael
Medium | Bob
Big | Bob
Big | Joe
Big | Michael
至此表:
Ship To Name | Equipment Desc
-------------+-------------
Bob | 1 Big, 1 Medium, 2 small
Dylan | 1 Small
Joe | 1 Big, 1 Medium, 1 Small
Ethan | 1 Small
Michael | 1 Big, 1 Medium
我尝试了旧帖子中的 powerquery 解决方案。
https://superuser.com/a/1054653/1136022
这是我的看法
= Table.Group(#"Changed Type", {"Ship To Name"}, {{"Installed Base", each Text.Combine([Equipment Desc], "#(cr)"), type text}})
但它不会汇总所有工具。它只是返回一个工具列表。
即它不会显示“2 大”,而是显示“1 大,1 大”。
提前感谢您的考虑。
此致,
托马斯
答案1
从本质上讲,这应该会让你相当接近:
- 按计数分组
- 合并列以合并计数和设备描述
- 设备描述中心
- 合并列
看起来你前面的步骤也被命名为#“更改类型”,所以你应该能够从那个点粘贴我的代码。集成别人的 M 代码总是有点麻烦。
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs5NzMlR0lFyyk9SitVB8F0qcxLzUES88lMVUARcSzLQlMAM8U1NySzNRdYDF/HNTM5ITM1BFYTpc8pMx+DBjYBw4fpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Equipment Desc" = _t, #"Ship To Name" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Ship To Name", type text}, {"Equipment Desc", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Ship To Name", "Equipment Desc"}, {{"Count", each Table.RowCount(_), type number}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Grouped Rows", "Equipment Desc", "Equipment Desc - Copy"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"Count", type text}}, "en-AU"),{"Count", "Equipment Desc - Copy"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Sorted Rows" = Table.Sort(#"Merged Columns",{{"Equipment Desc", Order.Ascending}}),
#"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[#"Equipment Desc"]), "Equipment Desc", "Merged"),
#"Merged Columns1" = Table.CombineColumns(#"Pivoted Column",{"Big", "Medium", "Small"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"Equipment Desc")
in
#"Merged Columns1"
答案2
以下是一种方法:
- 按两列进行分组
RowCount
<space>
使用分隔符合并设备和数量列- 按名称列分组
All Rows
- 添加自定义列以将结果表转换为列表
- 自定义列公式:
=Table.Column([Equipment],"Merged")
- 自定义列公式:
- 从列中提取值
List
(使用双箭头),
作为分隔符
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Equipment Desc", type text}, {"Ship To Name", type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"Ship To Name", Text.Trim, type text}, {"Equipment Desc", Text.Trim, type text}}),
#"Grouped Rows" = Table.Group(#"Trimmed Text", {"Ship To Name", "Equipment Desc"}, {{"Count", each Table.RowCount(_), type number}}),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Grouped Rows", {{"Count", type text}}, "en-US"),{"Count", "Equipment Desc"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),
#"Grouped Rows1" = Table.Group(#"Merged Columns", {"Ship To Name"}, {{"Equipment", each _, type table [Ship To Name=text, Merged=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows1", "Eqpt List", each Table.Column([Equipment],"Merged")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Equipment"}),
#"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Eqpt List", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
#"Extracted Values"