March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I have a table with some data missing.
Neighborhood | Street |
East | Maple Rd |
West | Pine Rd |
East | Birch St |
East | Maple Rd |
Maple Rd | |
West | Pine Rd |
West | Oak 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:
Neighborhood | FullNeighborhood | Street |
East | East | Maple Rd |
West | West | Pine Rd |
East | East | Birch St |
East | East | Maple Rd |
Maple Rd | ||
West | West | Pine Rd |
West | West | Oak St |
Solved! Go to Solution.
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
_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:
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
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
_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:
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
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
Proud to be a 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.
Proud to be a Super User! | |
Could you walk me through how that GROUP BY would work?
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"?
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:
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 |
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
87 | |
84 | |
76 | |
49 |
User | Count |
---|---|
163 | |
148 | |
103 | |
74 | |
55 |