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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ReadTheIron
Helper III
Helper III

Cleaning Data with FirstNonBlank

I have a table with some data missing.

 

NeighborhoodStreet        
EastMaple Rd
WestPine Rd
EastBirch St
EastMaple Rd
 Maple Rd
WestPine Rd
WestOak St

 

I want to create a column that will fill in the blanks based on the complete entries. I'm pretty sure I can use FirstNonBlank to do this, but I can't figure out the expression to use. I tried FullNeighborhood = FIRSTNONBLANK(Table[Neighborhood], [Street]) but my table just looks like this:

 

NeighborhoodFullNeighborhoodStreet        
EastEastMaple Rd
WestWestPine Rd
EastEastBirch St
EastEastMaple Rd
  Maple Rd
WestWestPine Rd
WestWestOak St
1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi @ReadTheIron ,

According to your description, You want to create a column based on the [Street] field to complete the [Neighborhood] field. Right?

Here are the steps you can follow:

(1)This is my test data:

vyangliumsft_0-1661926934167.png

(2)We can create a calculated column : “FullNeighborhood”

FullNeighborhood =
var _street='Table'[Street]
var _fullnei=MAXX( FILTER('Table','Table'[Street]=_street), [Neighborhood])
return
    _fullnei

The other way is :

FullNeighborhood2 = var _cuurent_street='Table'[Street]
var _table=SELECTCOLUMNS( FILTER('Table','Table'[Street]=_cuurent_street) , "Neighborhood" ,[Neighborhood])
var _first_non=LASTNONBLANK(_table,[Neighborhood])
return
_first_non

(3)The result is as follows:

vyangliumsft_1-1661926934172.png

If this method can't meet your requirement, can you provide some special input and output examples? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

If you need pbix, please click here.

case_01_0831.pbix

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

6 REPLIES 6
v-yangliu-msft
Community Support
Community Support

Hi @ReadTheIron ,

According to your description, You want to create a column based on the [Street] field to complete the [Neighborhood] field. Right?

Here are the steps you can follow:

(1)This is my test data:

vyangliumsft_0-1661926934167.png

(2)We can create a calculated column : “FullNeighborhood”

FullNeighborhood =
var _street='Table'[Street]
var _fullnei=MAXX( FILTER('Table','Table'[Street]=_street), [Neighborhood])
return
    _fullnei

The other way is :

FullNeighborhood2 = var _cuurent_street='Table'[Street]
var _table=SELECTCOLUMNS( FILTER('Table','Table'[Street]=_cuurent_street) , "Neighborhood" ,[Neighborhood])
var _first_non=LASTNONBLANK(_table,[Neighborhood])
return
_first_non

(3)The result is as follows:

vyangliumsft_1-1661926934172.png

If this method can't meet your requirement, can you provide some special input and output examples? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

If you need pbix, please click here.

case_01_0831.pbix

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

ToddChitt
Super User
Super User

You could do it in Power Query. Right click on the source table and select DUPLICATE. Take that one, filter out the NULLs, then apply a GROUP BY step, (GROUP BY the two columns in question) adding something like a COUNT ROWS aggretation.

You could also do it in DAX with a SUMMARIZE function:

My Definitive table = SUMMARIZE ( 'Source table name', <first group by column>, <second group by column>, <Aggregation Name 1>, <aggregation operation 1>)

SUMMARIZE function (DAX) - DAX | Microsoft Docs

 




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





ToddChitt
Super User
Super User

So you need a difinitive source of where each Street is located. Maple is East, Oak is West, etc. To get that, copy your table, filter the blanks, then do a GROUP BY. Hopefully you won't have on Street located in two neighborhoods. That becomes your definitive source. Join that back to the list on Street. 




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Could you walk me through how that GROUP BY would work?

ToddChitt
Super User
Super User

Power Query FILL DOWN might help.

What are you expecting to be seen in that blank cell? "East" because that row is also "Maple Rd" and the one before it is "Maple Rd"?




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





Yes, I'm expecting to see "East", because the other instances of "Maple Rd" have "East" in the neighborhood field. I need something that isn't position-dependent, though, my data might also look like:

 

NeighborhoodStreet               
WestOak St
 Maple Rd
EastBirch St
 Oak St
WestPine Rd
EastMaple Rd

 

And I want to create a column like

NeighborhoodFullNeighborhoodStreet          
WestWestOak St
 EastMaple Rd
EastEastBirch St
 WestOak St
WestWestPine Rd
EastEastMaple Rd

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors