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

Thanks. This worked. And so did SundarRaj's reply. Got a quesition for AlienSx.

 

I would like to add more columns. So instead of only the first 3 columns being repeated on each row (like in my initial example) I would like 9 columns to be repeated on each row. So i will add 6 more columns before column Adj_Due _Date1. What would be the solution for that?

 

And what if then i added another column and made it 10?

 

Thanks once again

  • 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.