Shift Down all the columns in a table dynamically

[Mic1979]

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.

[v-bmanikante]

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

[slorin]

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 

[jgeddes · Accepted solution]

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.

[Mic1979]

Thanks! 😊

[Akash_Varuna]

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]

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]

hi

try this

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

change XXX to your source

br

MCG