Forum Discussion
Solved
unpivot Column
i have data in tab Input sheet in need the result in tab Output sheet
data in Excel : https://www.dropbox.com/scl/fi/4ucmiwijhu253sbg043d5/Data.xlsx?dl=0&rlkey=grbj0mom9w3tj53dz76padjtf
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZhPbxMxEMW/S861Yo+9/44hUIXQlJIgLlUPoYq4VFAhgsS3x7v27M7sjr0LUiTL1fYXP8+z520eH1cftpv1Vu3e3W3u1Be7Pq1vlTbro1kfjPp8f1KgtV7drEwYrNq8/vR/A+Mn7aC0U8YMD6RxoLab0w55gLzD+Q/y/CePsDJif/3OEE83siyIsgQIaHX/43dL0SjM+E+RWRKkVXna28tzT7N0lseJCj1uf+4lmobO0mptC7QPUW4TUHEwoDbXb7yOtTI2s76Ii3rbaqMtuiUWuH8dsKGzGaLtbNYuTLVPVz3wcO6d5mvBfKfTsouWWjwE8xpuXq0+Pv8iRfbfqBVUmTVGGnS0Aor2uTgAdUxNJ/GBDC9oLsNzJfJuL19RYkknabEl1hjF0pqYUY07tTqntsQad7gCSzIA/9nVZTwkAxDaDRJ4Da23qPj9m92xZVb0GNsqlDYMDiX3Bfa2hkZeIOeBzDtdXnteTWezQCsDiQMdNU1Wcs0kQyBC3MT99YVLLhUkLi7OA5lHttDSySzPyjyyaQXfwZStG+w/x9FNaLBdDGKdsjkDNmjAKSq2DZ3vPE2UJgJeGCAhx2gixwZEHGBavNpfjOnlRBaILFKrasaqhGVFFvl35tm0SjOoNHXYoXrqqCCyUlBnFmYGkWNUXFfsMnl3EpaVWESWbw1LnGm6Lg1tU919Uga7adOHo+ntk61mxEHATe5v0gMMbwid4/qPsF6XTHI4lNhS6VmSm6GbzXGeRk8mO6d5nJx4Sno6fRYgs7RYYIlnElGAta+uH4KvYWaFkM08LJOwyQwvSq4Dpha6dVZmwWIsRHGYmsYatdconjVGgwSNJE3f/kYWTONsAkcAFd+wlNiSiXUB5GIdpIICZFZXMrFjGhHreMbO0WyCxrTm05zj0cGghV10muudJ72laCdeCQuuiGy80ClH+U22MYZPNyabMLRgK+Po5ixg8pSx4A5smIV4D9fi+yxuaJomXoKeNvM263imkBmzr7NuFCv83W6LeNKKdNFyXh5yRfueg8F/wNGCVQvugSFbIA8Yj3a2+dPB8kW4i4NeHKQq6tzyWCsbwxYV0bD2NUYsrSH/SSIEE6j/UxT/RWIEWyaK/woxQkxEPf0F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Payment_ID1 = _t, Payment1 = _t, #"Due Date1" = _t, #"Actual Payment Date1" = _t, Remaining1 = _t, Payment_ID2 = _t, Payment2 = _t, #"Due Date2" = _t, #"Actual Payment Date2" = _t, Remaining2 = _t, Payment_ID3 = _t, Payment3 = _t, #"Due Date3" = _t, #"Actual Payment Date3" = _t, Remaining3 = _t]), #"Grouped Columns" = List.Split(Table.ToColumns(Source),5), Custom1 = let cols = List.Transform(List.FirstN(Table.ColumnNames(Source), 5), each Text.Remove(_, {"0".."9"})) in Table.Combine(List.Accumulate({1..List.Count(#"Grouped Columns")}, {}, (s,c) => s & {Table.TransformColumns(Table.Unpivot(Table.FromColumns(#"Grouped Columns"{c-1}, cols), {"Payment"}, "PMT", "Amount"), {"PMT", each _ & Text.From(c)})})), #"Filtered Rows" = Table.SelectRows(Custom1, each ([Payment_ID] <> "")) in #"Filtered Rows"
2 Replies
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZhPbxMxEMW/S861Yo+9/44hUIXQlJIgLlUPoYq4VFAhgsS3x7v27M7sjr0LUiTL1fYXP8+z520eH1cftpv1Vu3e3W3u1Be7Pq1vlTbro1kfjPp8f1KgtV7drEwYrNq8/vR/A+Mn7aC0U8YMD6RxoLab0w55gLzD+Q/y/CePsDJif/3OEE83siyIsgQIaHX/43dL0SjM+E+RWRKkVXna28tzT7N0lseJCj1uf+4lmobO0mptC7QPUW4TUHEwoDbXb7yOtTI2s76Ii3rbaqMtuiUWuH8dsKGzGaLtbNYuTLVPVz3wcO6d5mvBfKfTsouWWjwE8xpuXq0+Pv8iRfbfqBVUmTVGGnS0Aor2uTgAdUxNJ/GBDC9oLsNzJfJuL19RYkknabEl1hjF0pqYUY07tTqntsQad7gCSzIA/9nVZTwkAxDaDRJ4Da23qPj9m92xZVb0GNsqlDYMDiX3Bfa2hkZeIOeBzDtdXnteTWezQCsDiQMdNU1Wcs0kQyBC3MT99YVLLhUkLi7OA5lHttDSySzPyjyyaQXfwZStG+w/x9FNaLBdDGKdsjkDNmjAKSq2DZ3vPE2UJgJeGCAhx2gixwZEHGBavNpfjOnlRBaILFKrasaqhGVFFvl35tm0SjOoNHXYoXrqqCCyUlBnFmYGkWNUXFfsMnl3EpaVWESWbw1LnGm6Lg1tU919Uga7adOHo+ntk61mxEHATe5v0gMMbwid4/qPsF6XTHI4lNhS6VmSm6GbzXGeRk8mO6d5nJx4Sno6fRYgs7RYYIlnElGAta+uH4KvYWaFkM08LJOwyQwvSq4Dpha6dVZmwWIsRHGYmsYatdconjVGgwSNJE3f/kYWTONsAkcAFd+wlNiSiXUB5GIdpIICZFZXMrFjGhHreMbO0WyCxrTm05zj0cGghV10muudJ72laCdeCQuuiGy80ClH+U22MYZPNyabMLRgK+Po5ixg8pSx4A5smIV4D9fi+yxuaJomXoKeNvM263imkBmzr7NuFCv83W6LeNKKdNFyXh5yRfueg8F/wNGCVQvugSFbIA8Yj3a2+dPB8kW4i4NeHKQq6tzyWCsbwxYV0bD2NUYsrSH/SSIEE6j/UxT/RWIEWyaK/woxQkxEPf0F", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Payment_ID1 = _t, Payment1 = _t, #"Due Date1" = _t, #"Actual Payment Date1" = _t, Remaining1 = _t, Payment_ID2 = _t, Payment2 = _t, #"Due Date2" = _t, #"Actual Payment Date2" = _t, Remaining2 = _t, Payment_ID3 = _t, Payment3 = _t, #"Due Date3" = _t, #"Actual Payment Date3" = _t, Remaining3 = _t]), #"Grouped Columns" = List.Split(Table.ToColumns(Source),5), Custom1 = let cols = List.Transform(List.FirstN(Table.ColumnNames(Source), 5), each Text.Remove(_, {"0".."9"})) in Table.Combine(List.Accumulate({1..List.Count(#"Grouped Columns")}, {}, (s,c) => s & {Table.TransformColumns(Table.Unpivot(Table.FromColumns(#"Grouped Columns"{c-1}, cols), {"Payment"}, "PMT", "Amount"), {"PMT", each _ & Text.From(c)})})), #"Filtered Rows" = Table.SelectRows(Custom1, each ([Payment_ID] <> "")) in #"Filtered Rows"One of the options:
t1 = Table.SelectColumns(Source,{"A", "B", "C"}), t2 = Table.RenameColumns(Table.SelectColumns(Source,{"A1", "B1", "C1"}), {{"A1", "A"},{"B1", "B"},{"C1", "C"}}), t3 = Table.RenameColumns(Table.SelectColumns(Source,{"A2", "B2", "C2"}), {{"A2", "A"},{"B2", "B"},{"C2", "C"}}), res = Table.Combine({t1,t2,t3})If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
