Frequent Visitor

2 years ago

Solved

collect data from a column that changes position

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.

Need Help

dufoq3

2 years ago

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

6 Replies

dufoq3

Community Champion

2 years ago

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
Regular Visitor
2 years ago
so excellent solution!

zhqw
Regular Visitor
2 years ago
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 !
psvdr
Frequent Visitor
2 years ago
Dear zhqw & dufoq3 ,
Thank you so much for your efforts. Much appreciated.
Please give me some time to digest.

slorin

Super User

2 years ago

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

Anonymous
Not applicable
2 years ago
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

Forum Discussion

collect data from a column that changes position

6 Replies

Related content

Notebook Error: CANNOT_OPEN_SOCKET in collect()

DB2 - Package Collection

List Positions

Headers Changing Position

Previous Data Collection