Power Query - dynamically query group of columns (groups are delimited by blank columns)

[navafolk]

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 since product structure changes. However, they are always last group of columns in the data.

Please help me to focus query data of GROUP2 based on its dynamic group of columns delimited by blank column(s) (e.g. 3rd group of columns).

Thank you.

[Ahmedx]

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ahmedx]

pls 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
    Custom1

and watch my video you will find out how I did it

ColumnsSelected.mp4

[navafolk]

Thank 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.

[Anonymous]

Hi @navafolk 

I have offered a new solution below at the 5th message, you can refer it.

Best Regards!

Yolo Zhu

[Ahmedx · Accepted solution]

ok, pls try again

[navafolk]

work like charm, highly dynamic, I also learn Table.Buffer and Table.Profile from you. Many thanks.

[Ahmedx]

Welcome!

[Ahmedx]

 instead of [Min] try [Max]

[Anonymous]

Hi @navafolk 

You can create a blank query and input the following code to advanced editor

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]),
    #"Changed Type" = 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}}),
    data_1 = Table.SelectColumns(#"Changed Type",{"Date", "Product_1", "Product_2"}),
    data_2 = Table.SelectColumns(#"Changed Type",{"Date", "Product_1st", "Product_2st"}),
    #"Renamed Columns" = Table.RenameColumns(data_2,{{"Product_1st", "Product_1"}, {"Product_2st", "Product_2"}}),
    data_3 = Table.SelectColumns(#"Changed Type",{"Date", "Product_1nd", "Product_2nd"}),
    #"Renamed Columns1" = Table.RenameColumns(data_3,{{"Product_1nd", "Product_1"}, {"Product_2nd", "Product_2"}}),
    #"Appended Query" = Table.Combine({data_1, #"Renamed Columns", #"Renamed Columns1"}),
    #"Added Index" = Table.AddIndexColumn(#"Appended Query", "Index", 1, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each Duration.Days(List.Max(#"Added Index"[Date])-List.Min(#"Added Index"[Date]))+1),
    #"Inserted Modulo" = Table.AddColumn(#"Added Custom1", "Modulo", each Number.Mod([Index], [Custom]), type number),
    #"Added Custom" = Table.AddColumn(#"Inserted Modulo", "Group", each "Group"&Text.From(Table.RowCount(Table.SelectRows(#"Inserted Modulo",(x)=>x[Modulo]=[Modulo] and x[Index]<=[Index]))-1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Custom", "Modulo"})
in
    #"Removed Columns"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[navafolk]

Thank you @Anonymous, very good idea from you.

As I said, structure of product might change e.g. more products or less products.

Thus, step Rename, packing to data_2 and data_3 and appending may need an auto capture of dynamic product structure.

[Anonymous]

Hi @navafolk 

First you need to rename each product name like the following(power query not support the same column name)

Then you can refer to the followiing code to advanced editor in power query

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, Product1_1 = _t, Product1_2 = _t, Column1 = _t, Product2_1 = _t, Product2_2 = _t, Column2 = _t, Product3_1 = _t, Product3_2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Product1_1", Int64.Type}, {"Product1_2", Int64.Type}, {"Column1", type text}, {"Product2_1", Int64.Type}, {"Product2_2", Int64.Type}, {"Column2", type text}, {"Product3_1", Int64.Type}, {"Product3_2", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),
    #"Split Column by Position" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByPositions({0, 7}, false), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Attribute.2", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, false), {"Attribute.2.1", "Attribute.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.2.1", Int64.Type}, {"Attribute.2.2", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type2",{{"Attribute.2.1", Order.Ascending}, {"Attribute.2.2", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Filtered Rows1" = Table.SelectRows(#"Sorted Rows", each [Attribute.2.1] = List.Max(#"Sorted Rows"[Attribute.2.1])),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Filtered Rows1", {{"Attribute.2.1", type text}}, "en-US"),{"Attribute.1", "Attribute.2.1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Attribute.2.2", type text}}, "en-US"),{"Merged", "Attribute.2.2"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Merged.1"),
    #"Grouped Rows" = Table.Group(#"Merged Columns1", {"Merged.1"}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1), type table}}),
    #"Expanded Count1" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date", "Value", "Index"}, {"Date", "Value", "Index"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded Count1", List.Distinct(#"Expanded Count1"[Merged.1]), "Merged.1", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

It will only display the max group(group 2) data 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[navafolk]

Anyone, please help!

Any function to navigate to focused columns (GROUP2) and delete other columns, please?