Pivot / Unpivot?

[J_o_n_a_s]

Hi. I got this table below and i want to convert it to the table underneath it. I'm assuming to use Pivot/unpivot but can't figure out how. Thanks for your input!

PM	Pro_Key	Inv_No	Adj_Due _Date1	Adj_Sum1	Adj_Due _Date2	Adj_Sum2
James	Project1	101	01/07/2025	18825	01/08/2025	2000
John	Project2	102	01/06/2025	20080	01/07/2025	5020
PM	Pro_Key	Inv_No	Due Date	Sum
James	Project1	101	01/07/2025	18825
James	Project1	101	01/08/2025	2000
John	Project2	102	01/06/2025	20080
John	Project2	102	01/07/2025	5020

[v-pbandela-msft]

Hi @J_o_n_a_s,

Thank you for reaching out in Microsoft Community Forum.

Thank you @AntrikshSharma , @SundarRaj , @AlienSx , @uzuntasgokberk    for the helpful response.

As suggested by AntrikshSharma, SundarRaj, AlienSx, uzuntasgokberk.,  I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as Solution" and give it a 'Kudos' so others can find it easily.

Please continue using Microsoft community forum.

Regards,
Pavan.

[AntrikshSharma]

@J_o_n_a_s 

let
    Source = Table, 
    A = Table.CombineColumns (
        Source, 
        { "Adj_Due _Date1", "Adj_Due _Date2", "Adj_Sum1", "Adj_Sum2" }, 
        ( x ) as table =>
            Table.FromColumns (
                { 
                    List.Select ( x, ( y ) => y is date ), 
                    List.Select ( x, ( y ) => y is number ) 
                }, 
                type table [ Due Date = date, Sum = number ]
            ), 
        "x"
    ), 
    B = Table.ExpandTableColumn ( A, "x", { "Due Date", "Sum" }, { "Due Date", "Sum" } )
in
    B

let
    Source = Table,
    Unpivot = Table.UnpivotOtherColumns ( Source, { "PM", "Pro_Key", "Inv_No" }, "A", "V" ),
    Group = Table.Group (
        Unpivot,
        { "PM", "Pro_Key", "Inv_No" },
        {
            "T",
            ( x ) =>
                Table.FromColumns (
                    {
                        Table.SelectRows ( x, ( y ) => y[V] is date )[V],
                        Table.SelectRows ( x, ( y ) => y[V] is number )[V]
                    },
                    type table [ Due Date = date, Sum = number ]
                )
        }
    ),
    Expand = Table.ExpandTableColumn ( Group, "T", { "Due Date", "Sum" }, { "Due Date", "Sum" } )
in
    Expand

[J_o_n_a_s]

Thanks all. Will test them on monday 🙂

[SundarRaj]

Hi @J_o_n_a_s , here's another approach you can test out. I'll leave the code used below. Thanks!

Here's the code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"PM", type text}, {"Pro_Key", type text}, {"Inv_No", Int64.Type}, {"Adj_Due _Date1", type datetime}, {"Adj_Sum1", Int64.Type}, {"Adj_Due _Date2", type datetime}, {"Adj_Sum2", Int64.Type}}),
Cols = Table.ColumnNames ( #"Changed Type" ),
ReqCols = List.Select ( Cols , each not (try Number.From ( Text.End ( _ , 1 ) ) )[HasError] ),
Count = List.Count ( List.Distinct ( List.Transform ( ReqCols , each Text.End ( _ , 1 ) ) ) ),
List = Table.ToColumns ( Table.SelectColumns ( #"Changed Type" , ReqCols ) ),
Table = List.Transform ( List.Split ( List , Count ) , each Table.FromColumns ( _ , { "Due Date" , "Sum" } ) ),
FixedCols = Table.RemoveColumns ( #"Changed Type" , ReqCols ),
Index = Table.TransformColumns ( Table.AddIndexColumn ( FixedCols , "Index" ,0 , 1 ) , { "Index" , each Table{_} } ),
Expand = Table.ExpandTableColumn(Index, "Index", {"Due Date", "Sum"}, {"Due Date", "Sum"})
in
Expand

Sig,

[uzuntasgokberk]

Hello @J_o_n_a_s ,

You can implement this way. İf it is suitable for you.
I created a dummy data and shared m code you can implement easily.
Table1:
'

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMTS1W0lEKKMrPSk0uMQQyDQ1ApIGhvoG5vpGBkSlIyMICTIMELWCCRgYGBkqxOkAz8jPyEEYYgY0wgqo2Q1JtYYBurqmBEdCIWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PM = _t, Pro_Key = _t, Inv_No = _t, #"Adj_Due _Date1" = _t, Adj_Sum1 = _t, #"Adj_Due _Date2" = _t, Adj_Sum2 = _t]),

#"Changed column type" = Table.TransformColumnTypes(Source, {{"PM", type text}, {"Pro_Key", type text}, {"Inv_No", Int64.Type}, {"Adj_Due _Date1", type date}, {"Adj_Sum1", Int64.Type}, {"Adj_Due _Date2", type date}, {"Adj_Sum2", Int64.Type}}),

#"Removed other columns" = Table.SelectColumns(#"Changed column type", {"PM", "Pro_Key", "Inv_No", "Adj_Due _Date1", "Adj_Sum1"}),

#"Renamed columns" = Table.RenameColumns(#"Removed other columns", {{"Adj_Due _Date1", "Adj_Due _Date"}, {"Adj_Sum1", "Adj_Sum"}})

in

#"Renamed columns"

'

Table2:
'

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8krMTS1W0lEKKMrPSk0uMQQyDQ1ApIGhvoG5vpGBkSlIyMICTIMELWCCRgYGBkqxOkAz8jPyEEYYgY0wgqo2Q1JtYYBurqmBEdCIWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [PM = _t, Pro_Key = _t, Inv_No = _t, #"Adj_Due _Date1" = _t, Adj_Sum1 = _t, #"Adj_Due _Date2" = _t, Adj_Sum2 = _t]),

#"Changed column type" = Table.TransformColumnTypes(Source, {{"PM", type text}, {"Pro_Key", type text}, {"Inv_No", Int64.Type}, {"Adj_Due _Date1", type date}, {"Adj_Sum1", Int64.Type}, {"Adj_Due _Date2", type date}, {"Adj_Sum2", Int64.Type}}),

#"Removed other columns" = Table.SelectColumns(#"Changed column type", {"PM", "Pro_Key", "Inv_No", "Adj_Due _Date2", "Adj_Sum2"}),

#"Renamed columns" = Table.RenameColumns(#"Removed other columns", {{"Adj_Due _Date2", "Adj_Due _Date"}, {"Adj_Sum2", "Adj_Sum"}})

in

#"Renamed columns"

'
You can disable enable load.

Then, you can click append queries as new.

Append table:
'

let

Source = Table.Combine({Table, #"Table (2)"})

in

Source

'

Kind Regards,

Gökberk Uzuntaş

[AlienSx · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    tm = List.TransformMany(
        Table.ToList(Source, (x) => x), 
        (x) => List.Split(List.Skip(x, 3), 2), 
        (x, y) => List.FirstN(x, 3) & y
    ), 
    result = Table.FromList(tm, (x) => x)
in
    result

[J_o_n_a_s]

Thanks. This worked. And so did SundarRaj's reply. Got a quesition for @AlienSx.

I would like to add more columns. So instead of only the first 3 columns being repeated on each row (like in my initial example) I would like 9 columns to be repeated on each row. So i will add 6 more columns before column Adj_Due _Date1. What would be the solution for that?

And what if then i added another column and made it 10?

Thanks once again

[AlienSx]

Calculate position of Adj_Due_Date1 in the list of column names and replace 3 in my code with that number. 

[J_o_n_a_s]

Hi @AlienSx 

Sorry i am slow in this. In my original table, Adj_Due_Date1, is in position 4 not 3.

I replaced both 3s with 10. Then also with 11 and it didnt work. Got an error. THIS TABLE IS EMPTY.

[AlienSx]

List items in M are positioned starting with zero - maybe that's why. I have everything working correctly 

let
    Source = #table(
        {"0".."9"} & {"Adj_Due_Date1", "Adj_Sum1", "Adj_Due_Date2", "Adj_Sum2"},
        {{0..13}, {100..113}}
    ), 
    tm = ((position) => List.TransformMany(
        Table.ToList(Source, (x) => x), 
        (x) => List.Split(List.Skip(x, position), 2), 
        (x, y) => List.FirstN(x, position) & y
    ))(List.PositionOf(Table.ColumnNames(Source), "Adj_Due_Date1")), 
    result = Table.FromList(tm, (x) => x)
in
    result

Take care of correct column names parameter in Table.FromList yourself please.