Help on Missing data fill techniques

[gsksarepta]

Hi, I have table like this in PowerBI I need to fill the missing values in 4th column based on the respective values step3afin column.. for example. Here is the before after table I am hoping to get from this tranformation.. Copy down and copy up is not doing fill correctly because the non-empty value position varies. any help is appreciated. I am okay either new column or filling the exisiting column

		Table Before						Table after transformation
step1run	step2tff	step3afin	step4aex				step1run	step2tff	step3afin	step4aex	newresult
ab1	1tff1	ds1					ab1	1tff1	ds1		aeds2
ab1	1tff2	ds1					ab1	1tff2	ds1		aeds2
ab1	1tff3	ds1	aeds2				ab1	1tff3	ds1	aeds2	aeds2
ab1	1tff4	ds1					ab1	1tff4	ds1		aeds2
ab5	1tff5	ds5					ab5	1tff5	ds5		aeds6
ab5	1tff6	ds5	aeds6				ab5	1tff6	ds5	aeds6	aeds6
ab5	1tff7	ds5					ab5	1tff7	ds5		aeds6
ab5	1tff8	ds5					ab5	1tff8	ds5		aeds6
ab9	1tff9	ds9	aeds10				ab9	1tff9	ds9	aeds10	aeds10
ab9	1tff10	ds9					ab9	1tff10	ds9		aeds10
ab9	1tff11	ds9					ab9	1tff11	ds9		aeds10
ab9	1tff12	ds9					ab9	1tff12	ds9		aeds10
ab13	1tff13	ds13					ab13	1tff13	ds13		aeds14
ab13	1tff14	ds13					ab13	1tff14	ds13		aeds14
ab13	1tff15	ds13					ab13	1tff15	ds13		aeds14
ab13	1tff16	ds13	aeds14				ab13	1tff16	ds13	aeds14	aeds14
ab14	1tff17	ds14					ab14	1tff17	ds14
ab14	1tff18	ds14					ab14	1tff18	ds14
ab14	1tff19	ds14					ab14	1tff19	ds14
ab14	1tff20	ds14					ab14	1tff20	ds14
ab15	1tff21	ds15					ab15	1tff21	ds15		aeds16
ab15	1tff22	ds15					ab15	1tff22	ds15		aeds16
ab15	1tff23	ds15	aeds16				ab15	1tff23	ds15	aeds16	aeds16
ab15	1tff24	ds15					ab15	1tff24	ds15		aeds16

[v-binbinyu-msft · Accepted solution]

Hi @gsksarepta ,

Please try to create a new column with below dax formula:

newresult =
VAR _step3 = [step3afin]
VAR _a =
    CALCULATE (
        MAX ( 'Table'[step4aex] ),
        FILTER ( 'Table', [step3afin] = _step3 )
    )
RETURN
    _a

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gsksarepta]

Thanks for this, appreciate it. 

[amitchandak]

@gsksarepta , First use clean and trim on the column and post that replace '' (Means do not give any thing) with null

(All small case)

Then try fill up and down

Power Query Trim and Clean : https://youtu.be/NRYPsCnS0w4

Power Query Replace Value: https://youtu.be/hkZhZbR7Kmk

Fill Up Fill Down: https://youtu.be/mC2ps0pFqBI

Throwback- Learn Power BI: Replace Values, Text Operations, Append Tables, Merge Tables - https://youtu.be/cN8AO3_vmlY?t=37070

[gsksarepta]

Hi Amit @amitchandak , I didn't understand your response, do you mind adding some more details on what to do ?  Meanwhile, I will go through the links for ideas. Thanks for your time. 

[bhanu_gautam]

@gsksarepta , Try using this method

newresult =
VAR CurrentRow = YourTable[step4aex]
VAR NonEmptyValues =
FILTER(
YourTable,
YourTable[step3afin] = CurrentRow
&& NOT(ISBLANK(YourTable[step4aex]))
)
VAR Result =
IF(
COUNTROWS(NonEmptyValues) > 0,
CALCULATE(
MAXX(NonEmptyValues, NonEmptyValues[step4aex])
),
BLANK()
)
RETURN Result

Please accept as solution and give kudos if it helps

[gsksarepta]

Hi @bhanu_gautam Thanks for your time, I tried it on my PowerBI Table view added it as column measure but got this error, do you see any issues with the script or the way I did?  

[bhanu_gautam]

Try this

newresult =
VAR CurrentRow = YourTable[step4aex]
VAR NonEmptyValues =
VALUES (
FILTER (
YourTable,
YourTable[step3afin] = CurrentRow
&& NOT(ISBLANK(YourTable[step4aex]))
)
)
VAR Result =
IF (
COUNTROWS(NonEmptyValues) > 0,
MAXX(NonEmptyValues, [step4aex]),
BLANK()
)
RETURN
Result

[gsksarepta]

Hi, tried the new code but got a different error, attached my pbix file on this google drive, would it be possible to take a look and let me know ? Thanks again. 

https://drive.google.com/file/d/1gXOXu8-kSHrN_LjLnhlQ2az52wKCsmbT/view?usp=drive_link

[gsksarepta]

Thanks, I will try this and reach out.