How to remove cells containing null in PowerQuery?

[datatronics505]

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:

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

 

[jdellasala]

There is no need, but right click on one of the null cells and select Replace Values.

Leave the Replace With field empty empty and click OK.

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.

 

[GraH]

@datatronics505 , do you mean this?

Column1	Column2	Column3	Column4	Column5
STATE
	DATE
			TYPE OF OFFER
		NAME OF OFFER
FL				SPECIAL REMARKS
	2/11/1972
			N/A
		OMG
FL				none
	11/06/1986
			Non-negotiable
		Long and hard
				Liked it before
FL
	10/01/1988
			free
		whatever
				No offer for this guy

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

STATE	DATE	NAME OF OFFER	TYPE OF OFFER	SPECIAL REMARKS
FL	2/11/1972 0:00:00	OMG	N/A	none
FL	11/06/1986 0:00:00	Long and hard	Non-negotiable	Liked it before
FL	10/01/1988 0:00:00	whatever	free	No offer for this guy

 

[jdellasala]

@datatronics505 , do you mean this?

Column1	Column2	Column3	Column4	Column5
STATE
	DATE
			TYPE OF OFFER
		NAME OF OFFER
FL				SPECIAL REMARKS
	2/11/1972
			N/A
		OMG
FL				none
	11/06/1986
			Non-negotiable
		Long and hard
				Liked it before
FL
	10/01/1988
			free
		whatever
				No offer for this guy

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

STATE	DATE	NAME OF OFFER	TYPE OF OFFER	SPECIAL REMARKS
FL	2/11/1972 0:00:00	OMG	N/A	none
FL	11/06/1986 0:00:00	Long and hard	Non-negotiable	Liked it before
FL	10/01/1988 0:00:00	whatever	free	No offer for this guy

Hmmm. That's not what I got from your query.

Book1
	G	H	I	J	K
1	STATE	DATE	NAME OF OFFER	TYPE OF OFFER	SPECIAL REMARKS
2	FL	26605	OMG	N/A	none
3	FL	31574	Long and hard	Non-negotiable	Liked it before
4	FL	32152	whatever	free	No 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:

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
	G	H	I	J	K
1	STATE	DATE	NAME OF OFFER	TYPE OF OFFER	SPECIAL REMARKS
2	FL	11/02/1972	OMG	N/A	none
3	FL	06/11/1986	Long and hard	Non-negotiable	Liked it before
4	FL	01/10/1988	whatever	free	No offer for this guy
Sheet2

And just to confuse issues, my standard date is mm/dd/yyyy and yours is dd/mm/yy!

 

[datatronics505]

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?

 

[GraH]

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

 

[jdellasala]

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
	K	L	M
7	Date	Value
8	01/01/2020	18,989.877	18,989.877
9	04/01/2020		0.000
10	07/01/2020		0.000
11	10/01/2020		0.000
12	01/01/2021		0.000
13	04/01/2021		0.000
14	07/01/2021		0.000
15	10/01/2021		0.000
16	01/01/2022		0.000
17	04/01/2022	19,895.271	19,895.271
18	07/01/2022	20,039.406	20,039.406
19	Average:	19,641.518	5,356.778
Sheet1

Cell Formulas
Range	Formula
L19:M19	L19	=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.