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.
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.
- Mic19791 year ago
Post Partisan
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
ResultThis 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:
- MCG1 year ago
Helper I
hi
try this
= Table.FromList(List.Combine( List.Transform(Table.ToRecords(XXX),Record.ToList)))
change XXX to your source
br
MCG