Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    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.

  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous

     

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

     

    Regards,

    Cherie

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

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?

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      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.