Reply
Frequent Visitor
Posts: 14
Registered: ‎03-28-2018

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

Super User
Posts: 1,904
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,542
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?

Frequent Visitor
Posts: 14
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,542
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: 860
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