collect data from a column that changes position

[psvdr]

Dear All,

Asking for your kind help. I'd like to collect values from a column (named strawberry) that is missaligned (first, it is in the 2nd column, than it is in the 3rd column and than in the 4th column) in the dataset.

Input data is in the first 4 columns on the left hand side, expected output in the last column in the example table below.

It's always the strawberry, and there's always a header (=new items have arrived) before any alignment change.

new	items	have	arrived
apple	strawberry				strawberry
4	6					6
1	2					2
3	1					1
new	items	have	arrived
apple	orange	strawberry
1	2	1				1
2	2	3				3
new	items	have	arrived
apple	orange	pear	strawberry
3	1	5	1			1
1	2	1	4			4
1	3	2	7			7
2	4	6	3			3

As there are hundreds of thousands of rows, power query is the preferred way.

How can I achieve this?

Thanks in advance.

[slorin]

Hi @psvdr , @zhqw , @dufoq3 

Another solution without Table.Group

One item

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykstV9JRyixJzS0G0hmJZalAKrGoKLMsNUUpVidaKbGgIAckVlxSlFielFpUVAnkKIAxSNoEyDJDETEEsoxQRIyBLEMUEaJtzS9KzEvHYj2yRYZwESOoiDEFFhWkJhah2ofsBVMwjW67CVzEGCpqDncPLICMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
PositionOf = Table.AddColumn(Source, "strawberry", 
    each List.PositionOf(Record.ToList(_), "strawberry")),
Replace_Position = Table.ReplaceValue(PositionOf, -1, null, Replacer.ReplaceValue, {"strawberry"}),
FillDown = Table.FillDown(Replace_Position, {"strawberry"}),
Replace_Value = Table.ReplaceValue(FillDown, each Record.ToList(_), 
    each null, (p,r,z)=> try Number.From(r{p}) otherwise null, {"strawberry"})
in
Replace_Value

Many items

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykstV9JRyixJzS0G0hmJZalAKrGoKLMsNUUpVidaKbGgIAckVlxSlFielFpUVAnkKIAxSNoEyDJDETEEsoxQRIyBLEMUEaJtzS9KzEvHYj2yRYZwESOoiDEFFhWkJhah2ofsBVMwjW67CVzEGCpqDncPLICMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
My_List = List.Buffer({"strawberry","pear","apple","orange"}),
PositionOf = Table.AddColumn(Source, "My_List", 
     (x) => List.ReplaceValue(List.Transform(My_List, 
          each List.PositionOf(Record.ToList(x), _)), -1, null, Replacer.ReplaceValue)),
Replace_Position = Table.ReplaceValue(PositionOf, each List.NonNullCount([My_List]), null,
         (x,y,z) => if y>0 then x else z, {"My_List"}),
FillDown = Table.FillDown(Replace_Position, {"My_List"}),
Replace_Value = Table.ReplaceValue(FillDown, each Record.ToList(_), each null, 
        (p,r,z)=> Table.FromRows({List.Transform(p ?? List.Repeat({null},List.Count(My_List)),
             each try Number.From(r{_}) otherwise null)}, My_List), {"My_List"}),
Expand = Table.ExpandTableColumn(Replace_Value, "My_List", My_List, My_List)
in
Expand

Stéphane

[psvdr]

Dear @zhqw & @dufoq3 ,

Thank you so much for your efforts. Much appreciated.

Please give me some time to digest.

[dufoq3 · Accepted solution]

Hi @psvdr, another solution here:

Result

v1 (group transformation based on List.PositionOf)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykstV9JRyixJzS0G0hmJZalAKrGoKLMsNUUpVidaKbGgIAckVlxSlFielFpUVAnkKIAxSNoEyDJDETEEsoxQRIyBLEMUEaJtzS9KzEvHYj2yRYZwESOoiDEFFhWkJhah2ofsBVMwjW67CVzEGCpqDncPLICMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    GroupedRows = Table.Group(Source, {"Column1"}, {{"All", each 
        [ a = List.Transform(Table.ToColumns(_), (x)=> List.Contains(x, "strawberry")),
          b = List.PositionOf(a, true),
          c = Table.AddColumn(_, "strawberry", (x)=> if x[Column1] = "new" then null else Record.ToList(x){b}, type text)
        ][c], type table}},
    GroupKind.Local, (s,c)=> Byte.From(c[Column1] = "new") ),
    CombinedAll = Table.Combine(GroupedRows[All])
in
    CombinedAll

v2 (group transformation based on List.TransformMany)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WykstV9JRyixJzS0G0hmJZalAKrGoKLMsNUUpVidaKbGgIAckVlxSlFielFpUVAnkKIAxSNoEyDJDETEEsoxQRIyBLEMUEaJtzS9KzEvHYj2yRYZwESOoiDEFFhWkJhah2ofsBVMwjW67CVzEGCpqDncPLICMlWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    Ad_GroupHelper = Table.AddColumn(AddedIndex, "GroupHelper", each if [Column1] = "new" then [Index] else null, Int64.Type),
    FilledDown = Table.FillDown(Ad_GroupHelper,{"GroupHelper"}),
    GroupedRows = Table.Group(FilledDown, {"GroupHelper"}, {{"All", each 
        [ a = Table.ToColumns(_),
          b = List.TransformMany(a,
                (y)=> {List.Contains(y, "strawberry")},
                (x,y)=> List.Select(x, (w)=> y) ),
          c = {null} & List.Skip(List.Combine(b)),
          d = Table.FromColumns(a & {c})
        ][d], type table}}),
    CombinedAll = Table.Combine(GroupedRows[All]),
    RemovedColumns = Table.RemoveColumns(CombinedAll,{"Column5", "Column6"})
in
    RemovedColumns

[zhqw]

so excellent solution!

[zhqw]

I duplicate your data in excel like this

and transform it as a table which name is "表1"，then into power query.
the code like follow:

 let

    Source = Excel.CurrentWorkbook(){[Name="表1"]}[Content],

    ChangeType = Table.TransformColumnTypes(Source,{{"列1", type text}, {"列2", type text}, {"列3", type text}, {"列4", type text}}),

    Group = Table.Group(ChangeType,"列1",{"new",each _},

0,(x,y)=> Number.From( y ="new items have arrived")  ),

    Transform = Table.TransformColumns(Group,{"new",each

Table.InsertRows(

Table.DemoteHeaders(Table.DuplicateColumn(Table.PromoteHeaders(Table.PromoteHeaders(_)),"strawberry","strawberry-duplicated")),

0,

{[Column1="new items have arrived",Column2=null,Column3=null,Column4=null,Column5=null]}

)

}

),

    expand = Table.ExpandTableColumn(Transform, "new", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"}),

    DeleteCol = Table.RemoveColumns(expand,{"列1"})

in

    DeleteCol
the result like this

I wish this can help you !

[v-xinruzhu-msft]

Hi @psvdr 

I have some questions that

1.Is the data you have offered are in the same table? or they represent 3 tables?

2.Is the column name 'strawbeyy' or the  strawbeyy just a value in a column?

Best Regards!

Yolo Zhu