Help with advanced Power Query

It would probably be easier to change the datatype to decimal rather than dealing with percentage type but anyway here's what I did.

Duplicate the table.

'Group By' COD and sum the Percent column. Call the new column Add

You'll get this

You then add a custom column to subtract from 100%.

Use this code, in the formula :

Percentage.From("100%") -[Add]

 Change the type to Percent.

 

You can then Merge this table with the original table (join on COD).

Expand the Table column to return only the Custom column from the 2nd table.

It'll look like this:

 

and from there you can add a column with logic ''If Percent column is null use Table 2 custom else use Percent column"

 

Good luck.

Here is another way to do it.  It uses a modified Replace Values step to do it in one step. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

 

let
    Fonte = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYjMDVaVYHQjfCYjzSnNywAJGUAUWUAVG6AqM0UwwhiowRuI7wzXEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cod = _t, CR = _t, Percent = _t]),
    #"Tipo Alterado" = Table.TransformColumnTypes(Fonte,{{"Cod", Int64.Type}, {"CR", type text}, {"Percent", Percentage.Type}}),
    CustomReplace = Table.ReplaceValue(#"Tipo Alterado",null,each let thiscod = [Cod] in 1 - List.Sum(Table.SelectRows(#"Tipo Alterado", each [Cod] = thiscod)[Percent]),Replacer.ReplaceValue,{"Percent"}),
    #"Changed Type" = Table.TransformColumnTypes(CustomReplace,{{"Percent", Percentage.Type}})
in
    #"Changed Type"

 

Regards,

Pat