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
AlienSx's avatar
AlienSx
Super User
1 year ago

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's avatar
J_o_n_a_s
Helper I
1 year ago

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's avatar
    AlienSx
    Super User
    1 year ago

    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.