How to remove cells containing null in PowerQuery?

datatronics505

Board Regular
Joined
Nov 26, 2022
Messages
55
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
As a result of wrangling a badly formatted text file into submission in the form of an ordinary table via Power Query, I got a query with a lot of cells whose value is null. They are scattered everywhere. The filter tool at the column header isn't helping much:

null_values_everywhere.png

How can I remove all those null values and only the null values?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
There is no need, but right click on one of the null cells and select Replace Values.
1670682101921.png

Leave the Replace With field empty empty and click OK.
1670682143713.png

You can select multiple columns to do this, however if it's done on a numeric column that's been defined like a Whole Number, Decimal Number, etc., the type will go back to Any (ABC123) and have to be reset when done.
ALSO, Fill Down/Up will not work on blank cells, and you can't replace null in numeric columns with nothing - you must provide a value.
Nulls just show up as empty cells in a table or pivot table, so as annoying as they might be (and I agree), it's probably best to just leave them alone.
 
Upvote 0
@datatronics505 , do you mean this?
Column1Column2Column3Column4Column5
STATE
DATE
TYPE OF OFFER
NAME OF OFFER
FLSPECIAL REMARKS
2/11/1972
N/A
OMG
FLnone
11/06/1986
Non-negotiable
Long and hard
Liked it before
FL
10/01/1988
free
whatever
No offer for this guy

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Cols = Table.ColumnNames(Source),
    Clean_all_cols_as_sub = List.Accumulate(Cols, [], (R, C) => Record.AddField(R, C, Table.AddIndexColumn(Table.FromList(List.Transform(List.RemoveNulls(Table.Column(Source,C) ), each Text.From(_) ) )  , "Index", 1,1) ) ),
    Rec_as_table = Record.ToTable(Clean_all_cols_as_sub),
    Expand_subs = Table.ExpandTableColumn(Rec_as_table, "Value", {"Column1", "Index"}, {"Column1", "Index"}),
    Pivot_cols = Table.Pivot(Expand_subs, List.Distinct(Expand_subs[Name]), "Name", "Column1"),
    Remove_index = Table.RemoveColumns(Pivot_cols,{"Index"}),
    Promote_headers = Table.PromoteHeaders(Remove_index, [PromoteAllScalars=true])
in
    Promote_headers
STATEDATENAME OF OFFERTYPE OF OFFERSPECIAL REMARKS
FL2/11/1972 0:00:00OMGN/Anone
FL11/06/1986 0:00:00Long and hardNon-negotiableLiked it before
FL10/01/1988 0:00:00whateverfreeNo offer for this guy
 
Upvote 0
@datatronics505 , do you mean this?
Column1Column2Column3Column4Column5
STATE
DATE
TYPE OF OFFER
NAME OF OFFER
FLSPECIAL REMARKS
2/11/1972
N/A
OMG
FLnone
11/06/1986
Non-negotiable
Long and hard
Liked it before
FL
10/01/1988
free
whatever
No offer for this guy

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Cols = Table.ColumnNames(Source),
    Clean_all_cols_as_sub = List.Accumulate(Cols, [], (R, C) => Record.AddField(R, C, Table.AddIndexColumn(Table.FromList(List.Transform(List.RemoveNulls(Table.Column(Source,C) ), each Text.From(_) ) )  , "Index", 1,1) ) ),
    Rec_as_table = Record.ToTable(Clean_all_cols_as_sub),
    Expand_subs = Table.ExpandTableColumn(Rec_as_table, "Value", {"Column1", "Index"}, {"Column1", "Index"}),
    Pivot_cols = Table.Pivot(Expand_subs, List.Distinct(Expand_subs[Name]), "Name", "Column1"),
    Remove_index = Table.RemoveColumns(Pivot_cols,{"Index"}),
    Promote_headers = Table.PromoteHeaders(Remove_index, [PromoteAllScalars=true])
in
    Promote_headers
STATEDATENAME OF OFFERTYPE OF OFFERSPECIAL REMARKS
FL2/11/1972 0:00:00OMGN/Anone
FL11/06/1986 0:00:00Long and hardNon-negotiableLiked it before
FL10/01/1988 0:00:00whateverfreeNo offer for this guy
Hmmm. That's not what I got from your query.
Book1
GHIJK
1STATEDATENAME OF OFFERTYPE OF OFFERSPECIAL REMARKS
2FL26605OMGN/Anone
3FL31574Long and hardNon-negotiableLiked it before
4FL32152whateverfreeNo offer for this guy
Sheet2

There were no data types defined in the output, and as a result the Date came in as Text and couldn't be easily converted once in the worksheet. The Date column also wouldn't go directly to a Date and had to first be converted to a Whole Number:
Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Cols = Table.ColumnNames(Source),
    Clean_all_cols_as_sub = List.Accumulate(Cols, [], (R, C) => Record.AddField(R, C, Table.AddIndexColumn(Table.FromList(List.Transform(List.RemoveNulls(Table.Column(Source,C) ), each Text.From(_) ) )  , "Index", 1,1) ) ),
    Rec_as_table = Record.ToTable(Clean_all_cols_as_sub),
    Expand_subs = Table.ExpandTableColumn(Rec_as_table, "Value", {"Column1", "Index"}, {"Column1", "Index"}),
    Pivot_cols = Table.Pivot(Expand_subs, List.Distinct(Expand_subs[Name]), "Name", "Column1"),
    Remove_index = Table.RemoveColumns(Pivot_cols,{"Index"}),
    Promote_headers = Table.PromoteHeaders(Remove_index, [PromoteAllScalars=true]),
    ChangeAnyDateToWholeNumber = Table.TransformColumnTypes(Promote_headers,{{"DATE", Int64.Type}}),
    DateToDateRestToText = Table.TransformColumnTypes(ChangeAnyDateToWholeNumber,{{"DATE", type date}, {"STATE", type text}, {"NAME OF OFFER", type text}, {"TYPE OF OFFER", type text}, {"SPECIAL REMARKS", type text}})
in
    DateToDateRestToText
Your Power Query chops are great, so this is no DIS. I noticed your queries tend to ignore the data type - you get right to the answer and don't bother with the niceties of "housecleaning". That's fine, and I get it. But just in case someone can't figure out why it's not working...
Book1
GHIJK
1STATEDATENAME OF OFFERTYPE OF OFFERSPECIAL REMARKS
2FL11/02/1972OMGN/Anone
3FL06/11/1986Long and hardNon-negotiableLiked it before
4FL01/10/1988whateverfreeNo offer for this guy
Sheet2

And just to confuse issues, my standard date is mm/dd/yyyy and yours is dd/mm/yy!
 
Upvote 0
Solution
There is no need, but right click on one of the null cells and select Replace Values.
View attachment 80595
Leave the Replace With field empty empty and click OK.
View attachment 80596
You can select multiple columns to do this, however if it's done on a numeric column that's been defined like a Whole Number, Decimal Number, etc., the type will go back to Any (ABC123) and have to be reset when done.
ALSO, Fill Down/Up will not work on blank cells, and you can't replace null in numeric columns with nothing - you must provide a value.
Nulls just show up as empty cells in a table or pivot table, so as annoying as they might be (and I agree), it's probably best to just leave them alone.
I have already tried that before I started this topic. Substitute null for something else and then try to wipe out that substitution text if null was some kind of holly text that Excel preserves at all cost. Still, Power Query will act out proportionally. Usable data parished along with null values.
To just leave them alone is definitely NOT an option. I will admit however, the structure of the text document I posed to myself as a problem to solve is inspired by a post I read here on this forum. It is not some kind of a deadline I have to meet nor a customer requiriment to chase. But to leave that alone, usable data scattered in the sea of null values, would you consider that acceptable even if as a toy task example?
 
Upvote 0
I do not always bother to post a solution where data types are defined. It's what's at the core that get's my focus.
In real life... Other situation. I always try to define the types without hardcoding values.
That's what Nice about the forum. Someone else will complete 😉
 
Upvote 0
I have already tried that before I started this topic. Substitute null for something else and then try to wipe out that substitution text if null was some kind of holly text that Excel preserves at all cost. Still, Power Query will act out proportionally. Usable data parished along with null values.
To just leave them alone is definitely NOT an option. I will admit however, the structure of the text document I posed to myself as a problem to solve is inspired by a post I read here on this forum. It is not some kind of a deadline I have to meet nor a customer requiriment to chase. But to leave that alone, usable data scattered in the sea of null values, would you consider that acceptable even if as a toy task example?
The null value is actually a numeric value. That is when it's in a numeric column, you can't replace it with nothing - the replacement must be numeric in a numeric field.
I don't understand the HOLY obsession with null. Do you have the same problem with blank cells? I don't understand the useable data PERISHED part either. That doesn't make sense in PQ.
The only sensible thing that could be done in a numeric column is to replace null with 0, however that would screw up statistics.
Book1
KLM
7DateValue
801/01/202018,989.87718,989.877
904/01/20200.000
1007/01/20200.000
1110/01/20200.000
1201/01/20210.000
1304/01/20210.000
1407/01/20210.000
1510/01/20210.000
1601/01/20220.000
1704/01/202219,895.27119,895.271
1807/01/202220,039.40620,039.406
19Average: 19,641.5185,356.778
Sheet1
Cell Formulas
RangeFormula
L19:M19L19=AVERAGE(L8:L18)

If your question hasn't been answered, try to be a little clearer on what you're trying to do - the end product, not what you perceive as the problem.
 
Upvote 0

Forum statistics

Threads
1,223,986
Messages
6,175,793
Members
452,670
Latest member
nogarth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top