Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a date column which has been poorly filled out... the date value for that particular date is entered in the first cell then a number of activities are then recorded (a time activity log) in the following rows. the next date is then entered and all the values inbeteen are zero. the number of rows between each new date value varies.
I want to make it so that every row has the corresponding date. i.e pull down the most recent non-zero value from above. the dates are also increasing as we go down the table.
A solution in a power BI format would be great or even excel!
Thanks
Alex
Solved! Go to Solution.
Hi @AlexValerio ,
Before that, we should insert the index column in power query like that.
Hi @AlexValerio ,
1.Replace 0 to null in power query.
2.Fill down.
M code for you reference.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDVNzIwMlDSQebE6gDliBMyQjbBCFM5WULGyIYaYypHEzJBVo7gxMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Date (current format)" = _t, #"New Date (What i want it to be like)" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date (current format)", type text}, {"New Date (What i want it to be like)", type date}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","0",null,Replacer.ReplaceValue,{"Date (current format)"}),
#"Filled Down" = Table.FillDown(#"Replaced Value",{"Date (current format)"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Date (current format)", type date}})
in
#"Changed Type1"
Alternatively, we can achieve that by dax. Before that, insert an index in power query and c reate a calculated column as below.
Column =
VAR i = 'Table (3)'[Index]
VAR minindex =
CALCULATE (
MAX ( 'Table (3)'[Index] ),
FILTER (
'Table (3)',
'Table (3)'[Index] < i
&& 'Table (3)'[Date (current format)] <> BLANK ()
)
)
RETURN
IF (
'Table (3)'[Date (current format)] = BLANK (),
CALCULATE (
MAX ( 'Table (3)'[Date (current format)] ),
FILTER ( 'Table (3)', 'Table (3)'[Index] = minindex )
),
'Table (3)'[Date (current format)]
)
Pbix as attached.
Hey Frank,
I want to keep zeros as zeros as they also have value in my data.
I tried your second option howwver i get a "Token Eof expected" error on the i on the second line of the code (in red)
VAR i = 'Table (3)'[Index]
Do you know why this is?
Hi @AlexValerio ,
Before that, we should insert the index column in power query like that.
Hey Frank, i already had that column added, see screenshot below.. out of curiosity, what does that i mean? should i be re-typing to code or is copy and paste fine?
Hi @AlexValerio ,
We should create calculated column in data view, Not custom column in power query. Please close and aplly and create the calculated column as the picture below.
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output.
Date (current format) | New Date (What i want it to be like) |
01/05/2020 | 01/05/2020 |
0 | 01/05/2020 |
0 | 01/05/2020 |
02/05/2020 | 02/05/2020 |
0 | 02/05/2020 |
0 | 02/05/2020 |
0 | 02/05/2020 |
03/05/2020 | 03/05/2020 |
0 | 03/05/2020 |
04/05/2020 | 04/05/2020 |
The left column is my current format and i want it to look like the righ column where the date is pulled down unti;l the next date is in a cell.
This option work in Edit Query or data transformation mode. make sure you use replace value to replace 0 with blank. That is also there on right-click
https://www.excelcampus.com/library/fill-down-blank-null-cells-power-query/
Again a dax code. here you can 0 in place of blank
https://community.powerbi.com/t5/Desktop/Fill-blanks-with-previous-value/td-p/492501
Appreciate your Kudos.
@AlexValerio , do above reply helped you. If note please let us known the issue and mark me @