Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cah2035
Frequent Visitor

For a null value, check if the value above and below are equal and if so, fill in the null value

Hi all,

 

I have to fill in dates for a huge number of rows. Most of the dates I was able to match with another table, for a decent amount, I was not. My records are in a particular order (essentially an index column) based on storage locations, and in that order, the records are mostly grouped by date (as in, all the records from a particular date are one after another). The null dates can be in the middle of these groups or at the end of the group. Here is an example:

 

 

IndexCategoryItemDate

1

AAlpha10/20/2020
2AOmega 
3ADelta10/20/2020
4BAlpha11/14/2022
5BOmega 
6CAlpha01/01/2020
7CBeta01/01/2020
8COmega 
9CGamma10/14/2021
10CBeta10/14/2021
11DOmega 
12DAlpha03/10/2014

 

"Omega" records are the ones I cannot match with a date and need to assume based on the other values. I need something that would do the following:

  • For Row 2, the date should be filled in as 10/20/2020 because Row 1 & Row 3 are both 10/20/2020 for the same category
  • For Row 5, the date should remain null because Row 4 & Row 6 are not the same category
  • For Row 8, the date should remain null becuase Row 7 & Row 9 are not the same date (though they are the same category)
  • For Row 11, the date should remain null because Row 10 & Row 12 are neither the same category nor the same date

To summarize, the only time I can assume is when the row above and below the null value are both the same category and the same date.

Any advice would be much appreciated!

1 ACCEPTED SOLUTION
BA_Pete
Super User
Super User

@cah2035 ,

 

You could try this. Not sure how performant it will be over a large dataset, but should do what you need:

-1- Add index starting from zero (called [IndexFrom0]).

-2- Add new custom column like this:

 

if [Date] = null
    and PreviousStepName[Date]{[IndexFrom0] - 1} = PreviousStepName[Date]{[IndexFrom0] + 1}
    and PreviousStepName[Category]{[IndexFrom0] - 1} = PreviousStepName[Category]{[IndexFrom0] + 1}
then PreviousStepName[Date]{[IndexFrom0] - 1}
else [Date]

 

Example output:

BA_Pete_0-1694015995823.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

7 REPLIES 7
AlienSx
Super User
Super User

Hi, @cah2035 the one with List.Generate

let
    Source = your_table,
    count = Table.RowCount(Source),
    rows = List.Buffer(Table.ToRecords(Source)),
    g = List.Generate(
        () => [i = 0, r = rows{0}],
        (x) => x[i] < count,
        (x) => 
            [i = x[i] + 1,
            r = 
                if not List.Contains({"", " ", null}, rows{i}[Date]) then rows{i} else 
                if List.AllTrue({x[r][Category] = rows{i + 1}[Category]?, x[r][Date] = rows{i + 1}[Date]?}) 
                    then Record.TransformFields(rows{i}, {"Date", each x[r][Date]}) else rows{i} ],
        (x) => x[r]
    ),
    z = Table.FromRecords(g)
in
    z
slorin
Super User
Super User

Hi,

Another solution with FillUp  and FillDown

 

let
Source = Your_Source,
#"Added Custom" = Table.AddColumn(Source, "Custom",
each if [Item]<>"Omega" then [Category]&"-"&Text.From([Date]) else null),
#"Duplicated Column" = Table.DuplicateColumn(#"Added Custom", "Custom", "Custom2"),
#"Filled Down" = Table.FillDown(#"Duplicated Column",{"Custom"}),
#"Filled Up" = Table.FillUp(#"Filled Down",{"Custom2"}),
#"Added Custom1" = Table.AddColumn(#"Filled Up", "Date2",
each if [Custom]=[Custom2] and [Date]=null then Date.From(Text.End([Custom],10)) else [Date], type date)
in
#"Added Custom1"

Stéphane 

BA_Pete
Super User
Super User

@cah2035 ,

 

You could try this. Not sure how performant it will be over a large dataset, but should do what you need:

-1- Add index starting from zero (called [IndexFrom0]).

-2- Add new custom column like this:

 

if [Date] = null
    and PreviousStepName[Date]{[IndexFrom0] - 1} = PreviousStepName[Date]{[IndexFrom0] + 1}
    and PreviousStepName[Category]{[IndexFrom0] - 1} = PreviousStepName[Category]{[IndexFrom0] + 1}
then PreviousStepName[Date]{[IndexFrom0] - 1}
else [Date]

 

Example output:

BA_Pete_0-1694015995823.png

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




@BA_Petethank you! This worked perfectly. I wasn't sure if there was a way to "offset" like you can in Excel but this acheives that.

 

What exactly is the function of {[IndexFrom0] + 1}  in general?

 

In Power Query, row numbers are identified behind the scenes by a zero-base integer list so, for example, row 5 of your category column can be identified as PreviousStepName[Category]{4} etc. Creating the [Index0] column effectively materialises this integer list for us to be able to dynamically identify row numbers as we scan down the table. Therefore, when we use PreviousStepName[ColumnName]{[IndexFrom0] + 1}, we're actually saying "Give me the value from the query table I'm transforming (PreviousStepName) in the [ColumnName] column where the row number is one greater than the current row being scanned", so it allows us to zero in on a specific cell in the table and grab that value.

 

Hope this makes sense.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thank you, I didn't know you could do that so that will be very helpful in the future!

 

No problem, happy to help.

Don't forget to give a thumbs-up on any posts that have helped you 👍

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors