动态旋转n行的新行，用于多个值

Question

我希望将输入转换为下面所示的输出：

​

​ noreferrer”>

sstatic.net/ppm9u.png“ rel =“ nofollow M代码复制上述内容：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Column1]), "Column1", "Column2")
in
    #"Pivoted Column"

我还尝试添加一个索引列，以使每个数据点都是唯一的，但这会导致进一步的问题。

到目前为止，我实际上已经对数据进行了分组，因此所有内容都包含在一个单元格中：

当前M代码：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type1" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Column1"}, {{"Column2", each Text.Combine([Column2],"#(lf)"), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Column1]), "Column1", "Column2")
in
    #"Pivoted Column"

但是，这需要拆分数据，但是<强>我想对n列I..e a​​b或a-az动态执行此操作，并且这种情况会改变问题，以便我必须动态拆分n列。

输入数据：

Column1 Column2
A   1
B   1
C   2
D   3
D   3
D   1
E   2
F   1
G   2
H   1
I   2
J   3
K   1
K   2
L   1
M   2
N   3

Answer 1

通过自定义功能解决的不寻常问题。检查信用中的链接以进行说明：

自定义功能

//credit: Cam Wallace  https://www.dingbatdata.com/2018/03/08/non-aggregate-pivot-with-multiple-rows-in-powerquery/

//Rename:  fnPivotAll 

(Source as table,
    ColToPivot as text,
    ColForValues as text)=> 

let
     PivotColNames = List.Buffer(List.Distinct(Table.Column(Source,ColToPivot))),
     #"Pivoted Column" = Table.Pivot(Source, PivotColNames, ColToPivot, ColForValues, each _),
 
    TableFromRecordOfLists = (rec as record, fieldnames as list) =>
    
    let
        PartialRecord = Record.SelectFields(rec,fieldnames),
        RecordToList = Record.ToList(PartialRecord),
        Table = Table.FromColumns(RecordToList,fieldnames)
    in
        Table,
 
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Values", each TableFromRecordOfLists(_,PivotColNames)),
    #"Removed Other Columns" = Table.RemoveColumns(#"Added Custom",PivotColNames),
    #"Expanded Values" = Table.ExpandTableColumn(#"Removed Other Columns", "Values", PivotColNames)
in
    #"Expanded Values"

主要代码

let

//change next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table24"]}[Content],

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    
    pivot = fnPivotAll(#"Changed Type","Column1","Column2")
in
    pivot

您的数据

Answer 2

这似乎可以正常工作（a）组并添加组内部（b）扩展（c）pivot

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"Column1"}, {{"data", each  Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type), type table}}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Column2", "Index"}, {"Column2", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded data", List.Distinct(#"Expanded data"[Column1]), "Column1", "Column2"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in  #"Removed Columns"