Merging multiple rows based on criteria into 1 in power query

Question

could you please assist solving the following tasks:

1. F.e. I have data set:

What i need - to create a task with description, which discounts need to be check. It should be in following format though: SKU within same brand with same discount depth should be merged into 1 row - Check Discount 10% for brand 1 for SKU's: cream & oil.

While others should remain as same rows as they have different discounts within brand:

Check Discount 20% for brand 2 for SKU detergent Check Discount 15% for brand 2 for SKU tabs.

There is more levels of data, f.e. the task should be within same outlet (if there is x > 1 outlets, task will be multiplied by x according to amount of outlets). But I guess it should be easy further on if I get the method how to do the mentioned above task.

2. Should be pretty similar to the previous one, but I might be wrong

Monitor & Catalogue columns basically describe which rows can be merged. So the output out of this table should be 2 rows:

1. Check positioning of 1-oil and 2-tabs on the monitor
2. Check positioning of 1-cream and 2-detergent on the catalogue

There can be multiple levels of aggregation, i.e. on top of rows with 1's, there can be rows with 2's - meaning they should be merged in separate task as well. 0 in all cases means - don't take.

I understand it might be a little bit overcomplicated, but i'm looking to speed up this process in Power Query as it's currently being done with VBA analyzing each row and finding match positions.

Here's the desired result with input data:

Everything further is simple. I just eliminate brand-sku and group by task.

Thank you!

Answer 1

Here's the first one - note that the format you requested in the first one (data entered into separate rows within the same cell using alt+enter) isn't supported in powerquery, so I separated the data with commas instead.

Instructions

1. Add column>add index column
2. Highlight index columns>transform>pivot>sku as values>advanced options>don't aggregate
3. Highlight all of the columns to the right>transform>merge columns (choose a separator if you want one, I chose commas)
4. Transform>Replace ,, with , (may have to do a few times)
5. Change Brand to text, Discount to %
6. Add column>custom column formula = "Check discount " & Number.ToText([Discount]*100) & "% for brand " & [Brand] & " for SKU " & Text.Trim([Merged],",")

Before/After

M Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", Int64.Type}, {"SKU", type text}, {"Discount", type number}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-US")[Index]), "Index", "SKU"),
    #"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"1", "2", "3", "4", "5"},Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Brand", type text}, {"Discount", Percentage.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each "Check discount " & Number.ToText([Discount]*100) & "% for brand " & [Brand] & " for SKU " & Text.Trim([Merged],","))
in
    #"Added Custom"

2nd Example Instructions

Note: For this one it is easies to do Monitor and Separator separately. Just filter for a different one each time.

1. Add column>add index column
2. Highlight index columns>transform>pivot>sku as values>advanced options>don't aggregate
3. Filter for Monitor =1
4. Delete Monitor & Catalogue columns
5. Merge remaining columns, use - as separator
6. Transpose
7. Merge columns using , as separator
8. Find and replace -- with - (may have to do a couple times)
9. Custom column> Use the formula ="Check place ment of " & Text.Trim([Merged]) & " on Monitor"

2nd Example Before/After

2nd Example M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", Int64.Type}, {"SKU", type text}, {"Monitor", Int64.Type}, {"Catalogue", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Index", {{"Index", type text}}, "en-US")[Index]), "Index", "SKU"),
    #"Filtered Rows" = Table.SelectRows(#"Pivoted Column", each ([Monitor] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Monitor", "Catalogue"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Removed Columns", {{"Brand", type text}}, "en-US"),{"Brand", "0", "1", "2", "3"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
    #"Transposed Table" = Table.Transpose(#"Merged Columns"),
    #"Merged Columns1" = Table.CombineColumns(#"Transposed Table",{"Column1", "Column2"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"Merged"),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Columns1","--","-",Replacer.ReplaceText,{"Merged"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","--","-",Replacer.ReplaceText,{"Merged"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each "Check place ment of " & Text.Trim([Merged]) & " on Monitor")
in
    #"Added Custom"

Hopefully that gets you started on how to apply PQ to your data! You may have to adjust slightly if your data sets vary.

Answer 2

Thanks for your advices, Hooded 0ne. Gave me the right direction. I've done only 1st part though, here are some adjustments I made:

1. Added $ to SKU to find position later to be replaced with "," - now I can clear the delimiters from merge in 1st step via replace ";" with blank, replace "$" with "," and Text.End or Trim the first "," in the row
2. Added Select Columns to the step after "Pivot columns". There's dynamic list of columns, so I can't hardcode "1,2,3,4,5" like you did

Here's my final code for p1:

    #"Add $" = Table.AddColumn(#"Filtered Rows", "SKU_SYMBOL", each "$"&[ROI_LKA_BASE.SKU]),
    #"Add Index" = Table.AddIndexColumn(#"Add $", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Add Index", "TextIndex", each "TASK_"&Number.ToText([Index])),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Index", "Shelf start", "Shelf End", "KAM", "Вид Инф. АУ", "Место размещ. АУ", "Адрес", "Attribute", "Value", "ROI_LKA_BASE.Мониторы", "ROI_LKA_BASE.Каталог", "ROI_LKA_BASE.Confirmed with customer", "ROI_LKA_BASE.Confirmed plan", "ROI_LKA_BASE.SKU"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns2", List.Distinct(#"Removed Columns2"[TextIndex]), "TextIndex", "SKU_SYMBOL"),
    ColumnsToSelect = List.Select(Table.ColumnNames(#"Pivoted Column"),each Text.Contains(_,"TASK")),
    #"Select Pivoted Columns" = Table.SelectColumns(#"Pivoted Column",ColumnsToSelect),
    #"Merged Columns" = Table.CombineColumns(#"Pivoted Column",Table.ColumnNames(#"Select Pivoted Columns"),Combiner.CombineTextByDelimiter(";", QuoteStyle.None),"PIVOT_MERGED"),
    #"Replaced Value" = Table.ReplaceValue(#"Merged Columns",";","",Replacer.ReplaceText,{"PIVOT_MERGED"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","$",",",Replacer.ReplaceText,{"PIVOT_MERGED"}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Value1", "TASK", each "Проверить скидку на " & [ROI_LKA_BASE.Бренд] & ": "
& Text.End([PIVOT_MERGED],Text.Length([PIVOT_MERGED])-1)),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"Promo ID", "promotool_code", "ROI_LKA_BASE.Начало акции", "ROI_LKA_BASE.Конец акции", "ROI_LKA_BASE.Chain code", "ROI_LKA_BASE.Описание промо", "ROI_LKA_BASE.Сеть", "TASK"}, {{"Count", each Table.RowCount(_), Int64.Type}}

Will return later with my solution on 2nd part. Thanks again Hooded 0ne, big help.

Answer 3

You guys are making it more complicated than it needs to be. The key here is that you can aggregate text columns when using Group By.

Here's how I'd do the first one:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUouSk3MBdKGBqpKsToQsfzMHCQRIyA7JbUktSg9Na8EyDZCEi9JTCoGKTUFCsUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Brand = _t, SKU = _t, Discount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", Int64.Type}, {"SKU", type text}, {"Discount", Percentage.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Brand", "Discount"}, {{"SKU", each Text.Combine([SKU],", "), type nullable text}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Result task", each "Check discount " & Number.ToText([Discount], "P0") & " for brand " & Number.ToText([Brand]) & " for SKU: " & [SKU], type text)
in
    #"Added Custom"

Result:

Note that I've grouped on Brand and Discount and aggregated the SKU column but combining each row into a list separated by ", " using Text.Combine([SKU],", ") as the aggregating function instead of any of the default options you can choose. I usually pick Max as the aggregation and then replace that function, i.e. List.Max([SKU]), in the formula for that step.

Once you've done that grouping, you just need to string the pieces together in a custom column.

---

The second one can be done similarly with the added step of concatenating Brand and SKU into one column before grouping.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUouSk3MBdIGQGyoFKsDEc3PzAHzQeIgMSMgKyW1JLUoPTWvBEU1SKYkMakYoTwWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Brand = _t, SKU = _t, Monitor = _t, Catalogue = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Brand", Int64.Type}, {"SKU", type text}, {"Monitor", Int64.Type}, {"Catalogue", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "BrandSKU", each Number.ToText([Brand]) & "-" & [SKU], type text),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Monitor", "Catalogue"}, {{"BrandSKU", each Text.Combine([BrandSKU], ", "), type text}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Result task", each "Check placement of " & [BrandSKU] & " on " & (if  [Catalogue] = 1 then "Catalogue" else "Monitor"), type text)
in
    #"Added Custom1"