Forum Discussion

J_o_n_a_s's avatar
J_o_n_a_s
Helper I
1 year ago
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 ...
Need Help
  • AlienSx's avatar
    AlienSx
    1 year ago
    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
SundarRaj's avatar
SundarRaj
Super User
1 year ago

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,