Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

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 others find it more quickly.

View solution in original post

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

image.png





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

Proud to be a PBI Community Champion




View solution in original post

8 REPLIES 8
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

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 others find it more quickly.
Anonymous
Not applicable

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

Hi @Anonymous ,

 

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 others find it more quickly.
PattemManohar
Community Champion
Community Champion

@Anonymous  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 PBI Community Champion




Anonymous
Not applicable

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

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

image.png





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

Proud to be a PBI Community Champion




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

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.