cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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 🙂
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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 135 members 1,549 guests
Please welcome our newest community members: