Forum Discussion
Solved
Pivot / Unpivot?
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 ...
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
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