Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi There
I am looking for a formula that will return the last non empty bench number for each equipment when the bench column is empty. I basically want to fill up all blanks in bench column with last non blank cell. There multiple EqpmtType and Eqpmt.
Regards
Solved! Go to Solution.
Hi @Anonymous
Try this
Column =
VAR __IntervalEnd = 'Table'[IntervalEnd]
VAR __previousBench =
CALCULATE(
FIRSTNONBLANK( 'Table'[Bench], 1 ),
ALLEXCEPT( 'Table', 'Table'[Eqmt] ),
'Table'[IntervalEnd] < __IntervalEnd
)
VAR __Bench = 'Table'[Bench]
RETURN
IF(
ISBLANK( __Bench ),
__previousBench,
__Bench
)
Hi
I was testing the code using filters which i think affect the calculations. When i checked the result without filtering the data, it works. Thank you
Hi @Anonymous
You can do it in Power Query, please see the code below and the attached file with a solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdBBDsMgDATAr1ScI8VeDBTfSj/Qe5T/f6NEatOkhogTHEar9S6LQ5oJMwh0Y1Kf3eReTyGi+oEE5vqW/HDrdLIEJWnZf7iFptFQ6RQwMGm4Do27BSkbiz30Bzl3YGHbNCvxqIWiaU1TrxIHQ/l4FX+sb4VWeO/D06agFixiNt2mklEbD1d9rbSaQoVbsCS7aZ3KXNUJBS5DT/uH3LXrGw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [IntervalEnd = _t, EqmtType = _t, Eqmt = _t, Bench = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"IntervalEnd", type datetime}, {"EqmtType", type text}, {"Eqmt", Int64.Type}, {"Bench", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","",null,Replacer.ReplaceValue,{"Bench"}),
#"Grouped Rows" = Table.Group(#"Replaced Value", {"Eqmt"}, {{"Count",
each
let
rows = _,
#"Sorted Rows" = Table.Sort(rows,{{"IntervalEnd", Order.Ascending}}),
#"Filled Down" = Table.FillDown(#"Sorted Rows",{"Bench"}),
#"Filled Up" = Table.FillUp(#"Filled Down",{"Bench"})
in
#"Filled Up",
type table [IntervalEnd=datetime, EqmtType=text, Eqmt=number, Bench=text]}}),
#"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"IntervalEnd", "EqmtType", "Bench"}, {"IntervalEnd", "EqmtType", "Bench"})
in
#"Expanded Count"
Hi@Mariusz
Thanks for you solution. However the table I'm working with is a summarized table from two other queries. I cannot edit the summarized table in Power Query.
May you kindly explain the logic that you used in the code and maybe i can achieve the same thing with a calculated column. I'm still very new to PowerBI, please bear with me.
Hi @Anonymous
Try this
Column =
VAR __IntervalEnd = 'Table'[IntervalEnd]
VAR __previousBench =
CALCULATE(
FIRSTNONBLANK( 'Table'[Bench], 1 ),
ALLEXCEPT( 'Table', 'Table'[Eqmt] ),
'Table'[IntervalEnd] < __IntervalEnd
)
VAR __Bench = 'Table'[Bench]
RETURN
IF(
ISBLANK( __Bench ),
__previousBench,
__Bench
)
Hi @Mariusz
The code does not seem to work properly, the screen shot below shows that it's returning B08 even though the first non blank was B7A
Hi
I was testing the code using filters which i think affect the calculations. When i checked the result without filtering the data, it works. Thank you
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |