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

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

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

v-cherch-msft Super Contributor
Super Contributor

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

7 REPLIES 7
Super User
Super User

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! Appreciate your Kudos Smiley Happy
Feel free to email me with any of your BI needs.





Super User
Super User

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?

Van2050 Regular Visitor
Regular Visitor

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
Super User

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

v-cherch-msft Super Contributor
Super Contributor

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

Van2050 Regular Visitor
Regular Visitor

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
Super User

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

You are welcome.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 46 members 793 guests
Please welcome our newest community members: