- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to add 1 to the previous row value if current row is null in POWERQuery
Hi All,
I am looking for workaround for the below cae .
Current row is null and we add 1 to the previous row value and put it in current row
I want to fill these null by adding 1 to the previous row value in Power Query
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Singh_10 ,
Based on your description, I created this data.
Please follow these steps:
1. Add a custom column.
if [Column1] = null then "NULL" else Text.From([Column1])
2.Grouped rows
3.Expand the data.
4.Choose column1 and fill down.
5. Add a custom column.
if [Custom] = "NULL" then [Column1] + [Index] else Number.FromText([Custom])
The end result is as shown in the image below, and you can remove the columns you don't need.
The overall M code is as follows:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRUitWJVjI2BlMIwtQUTcDMTCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] = null then "NULL" else Text.From([Column1])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}, GroupKind.Local),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Column1", "Index"}, {"Column1", "Index"}),
#"Filled Down" = Table.FillDown(#"Expanded Data",{"Column1"}),
#"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom2", each if [Custom] = "NULL" then [Column1] + [Index] else Number.FromText([Custom]))
in
#"Added Custom1"
If you have any other questions please feel free to contact me.
Best Regards,
Zhu
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Singh_10 ,
Based on your description, I created this data.
Please follow these steps:
1. Add a custom column.
if [Column1] = null then "NULL" else Text.From([Column1])
2.Grouped rows
3.Expand the data.
4.Choose column1 and fill down.
5. Add a custom column.
if [Custom] = "NULL" then [Column1] + [Index] else Number.FromText([Custom])
The end result is as shown in the image below, and you can remove the columns you don't need.
The overall M code is as follows:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRUitWJVjI2BlMIwtQUTcDMTCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] = null then "NULL" else Text.From([Column1])),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1, Int64.Type)}}, GroupKind.Local),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Column1", "Index"}, {"Column1", "Index"}),
#"Filled Down" = Table.FillDown(#"Expanded Data",{"Column1"}),
#"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom2", each if [Custom] = "NULL" then [Column1] + [Index] else Number.FromText([Custom]))
in
#"Added Custom1"
If you have any other questions please feel free to contact me.
Best Regards,
Zhu
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Good day @Singh_10 ,
An approach is to generate a new list for each column by iterating through each column and generating a value which is either the value in the original list or the previous value plus one. These new lists can then replace the original columns. Here is some code.
- The first thing to notice is the function fnFillNulls (which is towards the end of the code) - it takes a list (in this case a column) and generates a new list.
- fnFillNulls is called for the value_min column and the value_max column.
- A new table is constructed using the original table's headings as its headings but the two new lists as its columns.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYwMjUyMlfSAbEsLAwMlWJ1oiFsYyOwqLGRsYk5VNTYyMTMDCJqamluARbNK83JAQqBKVrwTQzAACgEZBmCWBiqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [value_min = _t, value_max = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"value_min", Int64.Type}, {"value_max", Int64.Type}}),
fillMins = fnFillNulls(#"Changed Type"[value_min]),
fillMaxs = fnFillNulls(#"Changed Type"[value_max]),
tblHeaders = Table.ColumnNames(#"Changed Type"),
tblColumns = {fillMins} & {fillMaxs},
combined = Table.FromColumns(tblColumns, tblHeaders),
#"Changed Type1" = Table.TransformColumnTypes(combined,{{"value_min", Int64.Type}, {"value_max", Int64.Type}}),
fnFillNulls = (col as list) as list =>
List.Generate( ()=>
[i=0, output=col{0}],
each [i] < List.Count(col),
each [i=[i]+1, output = if col{i}=null then [output]+1 else col{i}],
each [output]
)
in
#"Changed Type1"
The result is (I added rows after your data to show "return to normal" after a run of nulls).
Hope this helps.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @collinsg
Thank you for the reply.
I need this on the basis of ID-- need to group by ID-- one ID have multiple rows on the basis of dates where some dates ahve null values and i need to fill them by adding 1 to previous value min or max on the bases of date or datetime value and ID
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
07-21-2024 07:29 AM | |||
06-28-2024 05:00 AM | |||
07-09-2024 12:42 AM | |||
05-05-2023 12:47 AM | |||
10-29-2023 09:43 PM |
User | Count |
---|---|
103 | |
75 | |
44 | |
39 | |
32 |
User | Count |
---|---|
163 | |
90 | |
66 | |
46 | |
43 |