Forum Discussion
Shift Down all the columns in a table dynamically
- 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.
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:
hi
try this
= Table.FromList(List.Combine( List.Transform(Table.ToRecords(XXX),Record.ToList)))
change XXX to your source
br
MCG