Pivot / Unpivot?

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,

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ş

📌 If this post helps, then please consider Accepting it as a solution and giving Kudos — it helps other members find answers faster!

🔗 Stay Connected:
📘 Medium |
📺 YouTube |
💼 LinkedIn |
📷 Instagram |
🐦 X |
👽 Reddit |
🌐 Website |
🎵 TikTok |

Thanks all. Will test them on monday 🙂

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

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.