Populate First and Last Non Blank value

[Anonymous]

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-18

Aug-18

Sep-18

Oct-18

Nov-18

Dec-18

Jan-19

Feb-19

Mar-19

Apr-19

May-19

Jun-19

Jul-19

Null

7

Null

Null

Null

Null

Null

Null

Null

Null

Null

Null

Null

Null

Null

Null

Null

Null

Null

Null

5

Null

Null

Null

Null

Null

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

[v-frfei-msft · Accepted solution]

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

[v-frfei-msft · Accepted solution]

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]

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]

Hi @Anonymous ,

Right click the column and choose the option as below.

Regards,

Frank

[PattemManohar]

@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]

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.

Attribute	Value
Aug-18	Aug
Feb-18	Feb

[PattemManohar · Accepted solution]

@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]

Hi @Anonymous ,

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

Regards,
Frank

[Anonymous]

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