Forum Discussion

Mic1979's avatar
Mic1979
Post Partisan
1 year ago
Solved

Shift Down all the columns in a table dynamically

Dear all,

 

I have a table (the one on the top) and I would like to have the transformation as the one in the bottom

 

 

It is a matter of shifting all the rows down.

Could you help?

Thanks.

 

Need Help
  • jgeddes's avatar
    jgeddes
    1 year ago

    You can use Table.UnpivotOtherColumns for this.
    Select the first four columns of your table.

     

    On the 'Transform' ribbon choose 'Unpivot Columns' / 'Unpivot Other Columns'.

    Delete the created 'Attribute' column.

     

7 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    1 year ago

    You can use Table.UnpivotOtherColumns for this.
    Select the first four columns of your table.

     

    On the 'Transform' ribbon choose 'Unpivot Columns' / 'Unpivot Other Columns'.

    Delete the created 'Attribute' column.

     

  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    Hi Mic1979 For this, after loading the data into Power Query, add an index column to create a unique identifier for each row. Then, generate a new table with blank rows that match the number of shifts required and append it to the original data. Next, sort the combined data by the index to place the blank rows at the top. Finally, remove the index column and load the transformed table.

    • Mic1979's avatar
      Mic1979
      Post Partisan
      1 year ago

      Hello,

       

      thanks for your inputs.

      Looking for around in the web, I found the following solution:

       

      let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJSitWJVnIGslyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
      #"Renamed Columns" = Table.RenameColumns(Source,{{"Column2", "ColToMove"}}),

      OriginalColumnSequence = Table.ColumnNames(#"Renamed Columns"),
      OriginalTableType = Value.Type(#"Renamed Columns"),

      ColToMove = #"Renamed Columns"[ColToMove],
      MovedCol = List.Repeat({null},2)&ColToMove,

      RemovedColToMov = Table.RemoveColumns (#"Renamed Columns", {"ColToMove"}),
      TableToColumns = Table.ToColumns(RemovedColToMov),
      AddedMovedCol = TableToColumns&{MovedCol},

      ColumnNames = Table.ColumnNames(RemovedColToMov)&{"ColToMove"},
      TableFromColumns = Table.FromColumns(AddedMovedCol,ColumnNames),

      ReorderedColumns = Table.ReorderColumns(TableFromColumns,OriginalColumnSequence),
      RestoredOriginalTableType = Value.ReplaceType(ReorderedColumns,OriginalTableType),

      New_ColumnNames = Table.ColumnNames(RestoredOriginalTableType),

      ShiftNulls = Table.FromRecords(Table.TransformRows(
      RestoredOriginalTableType,
      (r) => Record.TransformFields(
      r,
      List.Accumulate({0..List.Count(New_ColumnNames) - 2},
      {},
      (state, current) => state & {{ColumnNames{current}, each if Record.Field(r, ColumnNames{0}) is null then Record.Field(r, ColumnNames{current + 1}) else _}}
      )
      )
      )),

      Result = Table.RemoveColumns(ShiftNulls, List.Last(New_ColumnNames))
      in
      Result

       

      This is allowing me to change this table:

      into this:

       

      However:

      1. I would like to shift down all the columns

      2. I don't know how to make this:

       

      • MCG's avatar
        MCG
        Helper I
        1 year ago

        hi

        try this

         

        = Table.FromList(List.Combine( List.Transform(Table.ToRecords(XXX),Record.ToList)))

         

        change XXX to your source

        br

        MCG

         

         

  • slorin's avatar
    slorin
    Super User
    1 year ago

    Hi Mic1979 

    Combine Columns + Expand

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("DcY3DQAwEARBLlebhHM2gdfzp+EtRhozRQUlZBRUNHR5MA02sbBxcPHk/gE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t]),
    CombineColumns = Table.CombineColumns(Source,{"Column5", "Column6", "Column7"},each _,"New_Column"),
    Expand = Table.ExpandListColumn(CombineColumns, "New_Column")
    in
    Expand

    Stéphane 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Mic1979 ,

     

    We would like to follow up to see if the solution provided by the super users resolved your issue. Please let us know if you need any further assistance.

     

    If our super users response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

    Please don't forget to give a "Kudos " – I’d truly appreciate it!

     

    Regards,

    B Manikanteswara Reddy