Custom column If statement to pickup above row value?

[Anonymous]

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

is it possible to achive this in Power BI ?

Thank you

Van

[Ashish_Mathur · Accepted solution]

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.

[Anonymous]

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

[Ashish_Mathur]

You are welcome.

[v-cherch-msft · Accepted solution]

Hi @Anonymous

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

Regards,

Cherie

[Ashish_Mathur]

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?

[Anonymous]

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!

Expected last columnThank you

Vans

[Ashish_Mathur · Accepted solution]

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.

[parry2k]

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