custom column with M calculating % with null values

[adavid999]

Hello,

In power query, how might I create a custom column "% column" where there are also null values in my reference columns A, B and C.

Without null values, it's straightforward. For row 1 I could write =([A]+[B]+[C])/3. However, what can I do where there are null values? Row 2 would be [A]+[B]/2 And Row 3 would be [B]+[C]/2

ID	% column	A	B	C
1	66	1	1	0
2	50	1	0	null
3	100	null	1	1

Many thanks,

A

[adavid999]

Thank you for replying. Your proposed solution looks useful but I would like a solution in M that can create the custom column if possible.

[v-easonf-msft · Accepted solution]

Hi, @adavid999 

It is difficult to create the custom columns if you want based on your current table structure.

You can try the following steps in the query editor:

1.select columns  "A","B","C" ->unpivot columns (generate new columns "Attribute","Value")

2.Group by as below

3.create Custom column"Result Percentage%" as below:

=[#"Sum value (by ID)"]/[#"Count Rows (by ID)"]

4.Expand table and delete unnecessary columns

5.select columns"Attribute" ,"Value" ->pivote column

Result will show as below:

Please check my sample pbix for more details.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi, @adavid999 

Please correct me if I wrongly understood your question.

I suggest unpivoting the table like below in Power Query Editor.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/