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