cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
datageek09 Frequent Visitor
Frequent Visitor

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Populate First and Last Non Blank value

Hi @datageek09 ,

 

We can split the column and remove the useless one.

 

Capture.PNG

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Populate First and Last Non Blank value

@datageek09  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"

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





8 REPLIES 8
Super User
Super User

Re: Populate First and Last Non Blank value

@datageek09  Hope you are looking for output like this..

 

image.png

 

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"


Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





datageek09 Frequent Visitor
Frequent Visitor

Re: Populate First and Last Non Blank value

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
Community Support Team
Community Support Team

Re: Populate First and Last Non Blank value

Hi @datageek09 ,

 

We can split the column and remove the useless one.

 

Capture.PNG

 

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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
datageek09 Frequent Visitor
Frequent Visitor

Re: Populate First and Last Non Blank value

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

Community Support Team
Community Support Team

Re: Populate First and Last Non Blank value

Hi @datageek09 ,

 

Right click the column and choose the option as below.

 

rel.PNG

 

Regards,

Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: Populate First and Last Non Blank value

@datageek09  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"

image.png



Did I answer your question? Mark my post as a solution !

Proud to be a Datanaut !





Community Support Team
Community Support Team

Re: Populate First and Last Non Blank value

Hi @datageek09 ,

 

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
datageek09 Frequent Visitor
Frequent Visitor

Re: Populate First and Last Non Blank value

thanks to both. worked out well and got my desired output.