cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DatNguyen
Regular Visitor

Add column with sepecific cell value

I want create new column A16_2 base on value of first row A16 which use flexible way:

IndexA16A16_2
035003500
1null3500
2null3500

I use Custom function from Edit Query in Excel.

I try some way like that:

  1. if [A16] = null then [A16]{0} else [A16]
  2. List.Average([A16])

But It NOT WORK. Could someone guide me how to create new column base on specificed value of 1 column?

1 ACCEPTED SOLUTION
Vera_33
Super User
Super User

Hi @DatNguyen 

 

When you call [A16], it is not a list, so what is your logic?

Vera_33_0-1641374967120.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTI2NTBQitWJVjIEcvJKc3LAHCM4JxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, A16 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"A16", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "yourNewColumn", each if [A16]=null then #"Changed Type"[A16]{0} else [A16]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each List.Average(#"Added Custom"[A16]))
in
    #"Added Custom1"

 

View solution in original post

1 REPLY 1
Vera_33
Super User
Super User

Hi @DatNguyen 

 

When you call [A16], it is not a list, so what is your logic?

Vera_33_0-1641374967120.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTI2NTBQitWJVjIEcvJKc3LAHCM4JxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, A16 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"A16", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "yourNewColumn", each if [A16]=null then #"Changed Type"[A16]{0} else [A16]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each List.Average(#"Added Custom"[A16]))
in
    #"Added Custom1"

 

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors