Help with advanced Power Query

[tarocha]

Hello

I'm new to Power BI and have some issues with Power Query.

I have the following scenario I have a table with 3 columns, cod, cr and percentage.

In the percentage column the sum of the cod always has to give 100%.

Example, cod 1.
We have 60% and the other is null, so what I would bring would be 40%, because 100% - 60% - 40%.

Example, cod 3.
We have 60% on the first line, 30% on the second line and on the third line I would like to bring the remaining 10%.

I need this to work on Power Query, I tried some solutions, but I couldn't.

Can you help me?

The result I hope is column D of Excel printing.

I share the pbix I'm using in the link below.

Link downalod pbix

https://drive.google.com/file/d/1DQrcplYeCtJZpGM4XNus3oTayt6Cktgx/view

Thank you

[HotChilli · Accepted solution]

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.

[mahoneypat · Accepted solution]

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

[tarocha]

@mahoneypat 
Thank you very much, your solution is sensational.
It solved my problem.

Thank you

[HotChilli · Accepted solution]

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.

[tarocha]

@HotChilli  Thank you very much, your solution is sensational.
It solved my problem.

Thank you