Forum Discussion
navafolk
Helper IV
Helper IVPower Query - dynamically query group of columns (groups are delimited by blank columns)
Hi pros, I have a product report of all GROUP by date, each GROUP is delimited by at least 1 blank column. It looks like: My focus is GROUP2, but columns of GROUP2 are moved quite often si...
ok, pls try again
Ahmedx
Super User
Super Userpls try this code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc7LDQAhCATQXjybCONnizH238aqQGTjHkgIPmF6D5w4gZBDDDwLs7QlaWlPR1wURvFL2dFstOqD/lAq94QWo6vatRV7KrQaLScAvtQCNKPtBHBbqwvw+AD52sp7OsYL", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Product_1 = _t, Product_2 = _t, Column1 = _t, Product_1nd = _t, Product_2nd = _t, Column2 = _t, Product_1st = _t, Product_2st = _t]),
from = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product_1", Int64.Type}, {"Product_2", Int64.Type}, {"Column1", type text}, {"Product_1st", Int64.Type}, {"Product_2st", Int64.Type}, {"Column2", type text}, {"Product_1nd", Int64.Type}, {"Product_2nd", Int64.Type}}),
ListColumns = {Table.ColumnNames( from){0}} & List.LastN( Table.ColumnNames( from),2),
Custom1 = Table.SelectColumns( from, ListColumns)
in
Custom1and watch my video you will find out how I did it
navafolkThank you, Ahmedx.
The product structure here is very dynamic, it can be 3 products, sometimes it has only 1 product.
ListColumns = {Table.ColumnNames( from){0}} & List.LastN( Table.ColumnNames( from),2),So, your ListColumns step (that took 2 last columns for GROUP2) may not dynamic enough to cover product structure changes.
