Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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.
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.
Thanks for this, appreciate it.
@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
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.
@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
Proud to be a Super User!
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?
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
Proud to be a Super User!
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
Thanks, I will try this and reach out.
User | Count |
---|---|
93 | |
83 | |
77 | |
74 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |