Hi, again
Bill writes some elegant code, but it's not entry level. When I use his approaches in solutions I create for others, I usually break them into several steps to the workbook owners can understand and maintain them.
I understand what you're looking for, but the answer is complicated. It would be easier if we could screen share and converse. But, since we can't do that I took a shot at expanding the step descriptions. Let me know if this helps.
Ok...
Using this annotated code:
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
04 ColD = List.Combine(
03 List.Transform(
01 List.FirstN(Source[D], each _ <> 0),
02 each List.Repeat({null},9) & {_})
),
07 TheEnd = Table.FromColumns(
06 Table.ToColumns(
05a Table.RemoveColumns(Source,{"D"})) & {ColD},
05b Table.ColumnNames(Source)
),
#"Reordered Columns" = Table.ReorderColumns(TheEnd,{"B", "A", "C", "D"})
in
#"Reordered Columns"
Summary:
01-Get the items that do not equal zero
02-Create a list of 9 nulls and the number for each non-zero item
Code:
List1
null
null
.
.
number
List2
null
.
.
etc
03-Replace the original Col_D items with the null padded lists
04-Combine all of the Col_D lists into one list
06-Create a list of lists containing the 05a column values
and the column names from 05b
05a-Remove Col_D from the Source and replace it with the list items
05b-Get the column names from Source
07-Convert the lists back into a table
Section Descriptions:
01-Get the items that do not equal zero
Code:
List.FirstN(Source[D], each _ <> 0)
Returns each Col_D item until in encounters a zero
02-Create a list of 9 nulls and the number for each non-zero item
Code:
each List.Repeat({null},9) & {a_Step01_Item}
Builds a list for each record that inserts 9 nulls
before each of the Step01 items
03-Replace the original Col_D items with the null padded lists
Code:
List.Transform(Using_Step01_Items, Step02_Item)
Replaces each of the NonZero items (Step01) with
the null-adjusted items (Step02)
04-Combine all of the Col_D lists into one list
Code:
List.Combine(Step03_List_of_Lists)
Converts the many lists from Step03
into a single list containing all of the items
05a-Remove Col_D from the Source and replace it with the list items
Used as a source by Step06:
Code:
Table.RemoveColumns(Source,{"D"})) & {ColD}
Removes Col_D from the Source and attaches
the new column
05b-Get the column names from Source
Code:
Table.ColumnNames(Source)
Used as a Column Name List by Step06:
Lists the column names from Source
06-Create a list of lists containing the column values
Code:
Table.ToColumns(Step05a_Columns,Step05b_col_names)
Converts each column from the Step05a table
to a list of the columns values. In the example,
the list will contain 4 lists records
(1: Col_A values, 2: Col_B values, 3: Col_C values, 4: Col_D values)
07-Convert the lists back into a table
Code:
Table.FromColumns(Step05a_Lists,Step05b_col_names)
