Forum Discussion

Sushmach109's avatar
Sushmach109
Icon for Helper I rankHelper I
8 years ago
Solved

filling blanks with previous column text

Hi team,

 

I need to fill the current blanks values with previous row data.

I tried but able to find the solution.Can you please try to give Solution.

 

Colulmn1

A

null

null

B

null 

null

 

Expected Result

 

Column

A

A

A

B

B

B

 

Thanks,

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi Sushmach109,

     

    Add an index column first.

     

    Create a calculated column with below DAX formula.

    Required Result =
IF (
    Table_1[Column2] = BLANK (),
    CALCULATE (
        LASTNONBLANK ( Table_1[Column2], 1 ),
        FILTER ( Table_1, Table_1[Index] < EARLIER ( Table_1[Index] ) )
    ),
    Table_1[Column2]
)

     

    Best regards,

    Yuliana Gu

5 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Sushmach109,

     

    Add an index column first.

     

    Create a calculated column with below DAX formula.

    Required Result =
IF (
    Table_1[Column2] = BLANK (),
    CALCULATE (
        LASTNONBLANK ( Table_1[Column2], 1 ),
        FILTER ( Table_1, Table_1[Index] < EARLIER ( Table_1[Index] ) )
    ),
    Table_1[Column2]
)

     

    Best regards,

    Yuliana Gu

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Sushmach109  you can use the fill down function in query editor

     

    go to the transform menu on the ribbon and select fill  and then down

    • Sushmach109's avatar
      Sushmach109
      Icon for Helper I rankHelper I
      8 years ago

      This is very dynamic and is not one time transformation.

      What is equalent dax formula for this?

       

      Column1 Column2     Required Result

       

      ticket1     A                 A

      ticket1    null               A

      ticket1    null               A

      ticket2    B                   B

      ticket2    null               B

      ticket2    null               B

      ticket2    C                   C

      ticket2    null                C

       

      Thanks,

      • vanessafvg's avatar
        vanessafvg
        Icon for Community Champion rankCommunity Champion
        8 years ago

        Sushmach109 the solution i posted is dynamic, whatever data is loaded will have that applied to it everytime