Enter a new row for IDs whose total percentage is less than 100

[Restu]

Hi all, 

I have a data that is kinda look like this

id	Merchandise	Percentage
a1	Toys	30
a1	Cosmetics	30
a1	Home appliances	30
a2	Toys	100

Basically, I want to automatically add a new row for each id that has a total percentage of less than 100 with the "Percentage" column filled with the value of difference between 100 and the total Percentage of that particular ID, and the "Merchandise" column will be automatically filled with "Other" as its value. The following table is the result that I intend to have:

id	Merchandise	Percentage
a1	Toys	30
a1	Cosmetics	30
a1	Home appliances	30
a1	Others	10
a2	Toys	100

Anybody has an idea on how to arrive with the second table? Thanks in advance.

Best,

Restu

[Anonymous · Accepted solution]

Sum all records by ID. Filter out anything not totaling to 100. Add column Merchandise and call it Other. Append the resulting table to the Source data. Sample is below:

let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"id"}, {{"Percentage", each List.Sum([Percentage]), type number}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Percentage] <> 100),
AddOther = Table.AddColumn(#"Filtered Rows", "Merchandise", each "Other"),
Combine = Table.Combine({Source , AddOther})
in
Combine

[Restu]

Thanks so much @dax and @Anonymous,

I read your messages and was inspired to:

1. Create a new table with a column that has the value of 100 - (aggregated value)
2. Append that table to the main table

Being a non-coder myself, I relied on Power Query's excellent GUI feature, I will detail what I did in the weekend so that a fellow non-coder can be benefited.

Cheers,

Restu  

[dax · Accepted solution]

Hi Restu,

You could try below M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjRU0lEKya8sBlLGBkqxOlAh5/zi3NSSzGR0cY/83FSFxIKCnMzEvORUZFkjhEGGBkCxWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, Merchandise = _t, Percentage = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"Merchandise", type text}, {"Percentage", Int64.Type}}),

#"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"Percentage", each List.Sum([Percentage]), type number}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Percentage] < 100),
AddOther = Table.AddColumn(#"Filtered Rows", "Merchandise", each "Other"),
Combine = Table.Combine({Source , AddOther}),
    #"Added Custom" = Table.AddColumn(Combine, "Custom", each (if [Merchandise]="Other" then  100-[Percentage] else  [Percentage])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Percentage"})
in
#"Removed Columns"

Best Regards,
Zoe Zhi

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

[Anonymous · Accepted solution]

Sum all records by ID. Filter out anything not totaling to 100. Add column Merchandise and call it Other. Append the resulting table to the Source data. Sample is below:

let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"id"}, {{"Percentage", each List.Sum([Percentage]), type number}}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Percentage] <> 100),
AddOther = Table.AddColumn(#"Filtered Rows", "Merchandise", each "Other"),
Combine = Table.Combine({Source , AddOther})
in
Combine