Forum Discussion
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 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.
ok, pls try again
12 Replies
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.
navafolkThank 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.
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.
- Ahmedx
Super User
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 Custom1and watch my video you will find out how I did it
- 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.
- Ahmedx
ok, pls try again
- navafolk
Anyone, please help!
Any function to navigate to focused columns (GROUP2) and delete other columns, please?
- Ahmedx
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
