Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Populate First and Last Non Blank value

Hi experts,

 

Need your help to solve a problem. I am trying to create a formula in PowerBI that allows me to return the first non blank value in a row, below is an excerpt of what I’m working with:

 

Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19
Null7NullNullNullNullNullNullNullNullNullNullNull
NullNullNullNullNullNullNull5NullNullNullNullNull

 

Essentially, I want to return Aug 18 for the first row and Feb 19  for the second row in a column.

 

Would appreciate your help.

 

thanks

 

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    We can split the column and remove the useless one.

     

     

    M code for your reference.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ivNyVHSUTIHYiiTGlSsTjTpOk2JMjkWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Jul-18" = _t, #"Aug-18" = _t, #"Sep-18" = _t, #"Oct-18" = _t, #"Nov-18" = _t, #"Dec-18" = _t, #"Jan-19" = _t, #"Feb-19" = _t, #"Mar-19" = _t, #"Apr-19" = _t, #"May-19" = _t, #"Jun-19" = _t, #"Jul-19" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Jul-18", type text}, {"Aug-18", type text}, {"Sep-18", type text}, {"Oct-18", type text}, {"Nov-18", type text}, {"Dec-18", type text}, {"Jan-19", type text}, {"Feb-19", type text}, {"Mar-19", type text}, {"Apr-19", type text}, {"May-19", type text}, {"Jun-19", type text}, {"Jul-19", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "Null")),
    #"Split Column by Position" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByRepeatedLengths(3), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"})
in
    #"Removed Columns"

     

    Regards,

    Frank

  • PattemManohar's avatar
    PattemManohar
    7 years ago

    Anonymous  In that case, use custom column in Power Query Editor as

     

    = Table.AddColumn(#"Filtered Rows", "ValueNew", each Text.Start([Attribute],3))

    So your final code will look like...

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ivNyVHSUTIHYiiTGlSsTjTpOk2JMjkWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Jul-18" = _t, #"Aug-18" = _t, #"Sep-18" = _t, #"Oct-18" = _t, #"Nov-18" = _t, #"Dec-18" = _t, #"Jan-19" = _t, #"Feb-19" = _t, #"Mar-19" = _t, #"Apr-19" = _t, #"May-19" = _t, #"Jun-19" = _t, #"Jul-19" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Jul-18", type text}, {"Aug-18", type text}, {"Sep-18", type text}, {"Oct-18", type text}, {"Nov-18", type text}, {"Dec-18", type text}, {"Jan-19", type text}, {"Feb-19", type text}, {"Mar-19", type text}, {"Apr-19", type text}, {"May-19", type text}, {"Jun-19", type text}, {"Jul-19", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "Null")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "ValueNew", each Text.Start([Attribute],3))
in
    #"Added Custom"

8 Replies

  • PattemManohar's avatar
    PattemManohar
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous  Hope you are looking for output like this..

     

     

    Please use the below code in Power Query Editor.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ivNyVHSUTIHYiiTGlSsTjTpOk2JMjkWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Jul-18" = _t, #"Aug-18" = _t, #"Sep-18" = _t, #"Oct-18" = _t, #"Nov-18" = _t, #"Dec-18" = _t, #"Jan-19" = _t, #"Feb-19" = _t, #"Mar-19" = _t, #"Apr-19" = _t, #"May-19" = _t, #"Jun-19" = _t, #"Jul-19" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Jul-18", type text}, {"Aug-18", type text}, {"Sep-18", type text}, {"Oct-18", type text}, {"Nov-18", type text}, {"Dec-18", type text}, {"Jan-19", type text}, {"Feb-19", type text}, {"Mar-19", type text}, {"Apr-19", type text}, {"May-19", type text}, {"Jun-19", type text}, {"Jul-19", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "Null"))
in
    #"Filtered Rows"
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi,

       

      Thanks for your reply. But I just need to return "month" name as column value instead of number. This is an audit report where I need to find how many audit's are currently running or gonna start for a specifc month.

       

      for example.

       

      AttributeValue
      Aug-18Aug
      Feb-18Feb
      • PattemManohar's avatar
        PattemManohar
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Anonymous  In that case, use custom column in Power Query Editor as

         

        = Table.AddColumn(#"Filtered Rows", "ValueNew", each Text.Start([Attribute],3))

        So your final code will look like...

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ivNyVHSUTIHYiiTGlSsTjTpOk2JMjkWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Jul-18" = _t, #"Aug-18" = _t, #"Sep-18" = _t, #"Oct-18" = _t, #"Nov-18" = _t, #"Dec-18" = _t, #"Jan-19" = _t, #"Feb-19" = _t, #"Mar-19" = _t, #"Apr-19" = _t, #"May-19" = _t, #"Jun-19" = _t, #"Jul-19" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Jul-18", type text}, {"Aug-18", type text}, {"Sep-18", type text}, {"Oct-18", type text}, {"Nov-18", type text}, {"Dec-18", type text}, {"Jan-19", type text}, {"Feb-19", type text}, {"Mar-19", type text}, {"Apr-19", type text}, {"May-19", type text}, {"Jun-19", type text}, {"Jul-19", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "Null")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "ValueNew", each Text.Start([Attribute],3))
in
    #"Added Custom"

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi Anonymous ,

     

    We can split the column and remove the useless one.

     

     

    M code for your reference.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ivNyVHSUTIHYiiTGlSsTjTpOk2JMjkWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Jul-18" = _t, #"Aug-18" = _t, #"Sep-18" = _t, #"Oct-18" = _t, #"Nov-18" = _t, #"Dec-18" = _t, #"Jan-19" = _t, #"Feb-19" = _t, #"Mar-19" = _t, #"Apr-19" = _t, #"May-19" = _t, #"Jun-19" = _t, #"Jul-19" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Jul-18", type text}, {"Aug-18", type text}, {"Sep-18", type text}, {"Oct-18", type text}, {"Nov-18", type text}, {"Dec-18", type text}, {"Jan-19", type text}, {"Feb-19", type text}, {"Mar-19", type text}, {"Apr-19", type text}, {"May-19", type text}, {"Jun-19", type text}, {"Jul-19", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Columns", each ([Value] <> "Null")),
    #"Split Column by Position" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByRepeatedLengths(3), {"Attribute.1", "Attribute.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Attribute.2"})
in
    #"Removed Columns"

     

    Regards,

    Frank

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      hello Frank,

       

      Appreciate your reply. I am not so familier with Power query, but is there any way i could replace value, ie "7" with month name, ie Aug?

       

      thanks

      • v-frfei-msft's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi Anonymous ,

         

        Right click the column and choose the option as below.

         

         

        Regards,

        Frank