Reply
Regular Visitor
Posts: 15
Registered: ‎03-28-2018
Accepted Solution

Custom column If statement to pickup above row value?

Hi there,

 

Im working with dirty data, missing rows, scattered columns!

i'll not complain more :-) ,

 

so trying to add data a custom column, i was able to achive this in excel using calculated column "X"

 

X4 =IF(A4= "teTNCSMV123",F4,X3)

X5 =IF(A5= "teTNCSMV123",F5,X4)

 

i am able to pickup first value (if True), is there anyway to get the second value (if False) ?

 

calculated column with IF.JPG

 

is it possible to achive this in Power BI ?

 

Thank you

Van


Accepted Solutions
Super User
Posts: 3,934
Registered: ‎01-14-2017

Re: Custom column If statement to pickup above row value?

Hi,

 

This M code works

 

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Coluxn1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Manual TAG", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Start([Coluxn1],5)="HdDte" then [Column6] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each [Manual TAG]=[Custom]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom.1"})
in
    #"Removed Columns"

Hope this helps.

View solution in original post

Community Support Team
Posts: 1,357
Registered: ‎07-25-2018

Re: Custom column If statement to pickup above row value?

Hi @Van2050

 

You may use 'Fill down' feature with your condition column.

1.png

 

Regards,

Cherie

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

View solution in original post


All Replies
Highlighted
Super User
Posts: 2,063
Registered: ‎07-22-2015

Re: Custom column If statement to pickup above row value?

@Van2050 in drop down "ABC123" select column option and that will do it for you.




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

www.perytus.com
(MVP Data Platform)


Super User
Posts: 3,934
Registered: ‎01-14-2017

Re: Custom column If statement to pickup above row value?

Hi,

 

In the Else portion, you want to pick up the value from the previous cell of the same column (in which you are writing that formula).  Seems like a tough one.  Can you anyways, share some data and also show the expected result.  Would you be OK with a DAX solution?

Regular Visitor
Posts: 15
Registered: ‎03-28-2018

Re: Custom column If statement to pickup above row value?

Hi @Ashish_Mathur @parry2k

Thank you for the response,

 

Here is screenshot

https://1drv.ms/u/s!Am-JUC7GnsuUtQncEobEaQpKzCmM

 

and sample in excel file

https://1drv.ms/x/s!Am-JUC7GnsuUtQhNQPqszL8u_cKb

 

Please let me know if it is possible to achive this using Power BI editor or DAX solution.

 

@parry2k  Custom column = Get value from other column

else

Get Same column above value!

 

Power BI sample.JPGExpected last columnThank you

Vans

Super User
Posts: 3,934
Registered: ‎01-14-2017

Re: Custom column If statement to pickup above row value?

Hi,

 

This M code works

 

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Coluxn1", type text}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Manual TAG", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Start([Coluxn1],5)="HdDte" then [Column6] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each [Manual TAG]=[Custom]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom.1"})
in
    #"Removed Columns"

Hope this helps.

Community Support Team
Posts: 1,357
Registered: ‎07-25-2018

Re: Custom column If statement to pickup above row value?

Hi @Van2050

 

You may use 'Fill down' feature with your condition column.

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor
Posts: 15
Registered: ‎03-28-2018

Re: Custom column If statement to pickup above row value?

Thank you @v-cherch-msft this is the easiest solution, I didn't know there is a "fill" option right out of the box

 

Thank you @Ashish_Mathur your code works like charm!

 

ill be using the M code for this instance, but both give the expected result.

 

Regards,

Van

Super User
Posts: 3,934
Registered: ‎01-14-2017

Re: Custom column If statement to pickup above row value?

You are welcome.