Forum Discussion
Cleaning Data with FirstNonBlank
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:
(2)We can create a calculated column : “FullNeighborhood”
FullNeighborhood = var _street='Table'[Street] var _fullnei=MAXX( FILTER('Table','Table'[Street]=_street), [Neighborhood]) return _fullneiThe 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:
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.
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
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"?
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:
| Neighborhood | Street |
| West | Oak St |
| Maple Rd | |
| East | Birch St |
| Oak St | |
| West | Pine Rd |
| East | Maple Rd |
And I want to create a column like
| Neighborhood | FullNeighborhood | Street |
| West | West | Oak St |
| East | Maple Rd | |
| East | East | Birch St |
| West | Oak St | |
| West | West | Pine Rd |
| East | East | Maple Rd |