Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 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"
Proud to be a PBI Community Champion
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
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.
Regards,
Frank
@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"
Proud to be a PBI Community Champion
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 |
@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"
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
thanks to both. worked out well and got my desired output.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |