Dynamically pivot n rows with new row for multiple values-CodePudding

I wish to transform the input to the output shown below:

However for Columns D and K there are multiple values for D and K which causes and error:

M Code to replicate the above:

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"

I have also attempted to add an index column so that each data point is unique but this leads to further problems.

So far I have actually grouped the data so that everything is contained in a single cell:

Current M Code:

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"

However this requires the data to be split which is fine but I want to do this dynamically for n Columns i..e A-B or A-AZ and this kind of shifts the problems so that I have to dynamically Split n columns.

Input data:

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

CodePudding user response：

A not unusual problem solved by a custom function. Check the link in the credits for an explanation:

Custom Function

//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"

Main Code

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

Results from your data