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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Andy964
Regular Visitor

Remove date from numeric column

Hi Community

This is my first post and am a newbi to Power BI. I'm testing some scenarios with data manipulation. Is it possible to remove any date entries from a numeric column? I have tried changing the data type to numneric but this changes the dates to numbers, all I want to do is remove the obvious date entry error values. Sorry if obvious, many thanks for your assistance.

1 ACCEPTED SOLUTION

As you don't proviode all required information, I assume that you want to have all non-number values in column "TotalCost" replaced by null.

 

You can copy the red part from the query below and paste it - in the advanced editor - behind your last-line-before-"in", replacing your last part:

in

     #"Promoted Headers"

 

Notice that also the comma behind "Source"  is red and must be copied.

 

let
    Source = #table(type table[TotalCost = number],{{1.2},{8},{#date(2017,9,1)},{#date(2017,10,4)}}),
    #"Promoted Headers" = Source,
    NoNumberToNull = Table.TransformColumns(#"Promoted Headers", {{"TotalCost", each if not (_ is number) then null else _, type number}})
in
    NoNumberToNull

 

Specializing in Power Query Formula Language (M)

View solution in original post

7 REPLIES 7
MarcelBeug
Community Champion
Community Champion

Assuming your date entries are actually dates, you can remove them in the query editor.

Otherwise I'm rather confused by "obvious date entries": what exactly do you mean by that?

 

Just filter the column on some value and then adjust the generated code like in the example below.
The first step just generates some test data.

To use the code below, you can create a new blank query, go into the advanced editor and replace the default code by the following code:

 

let
    Source = #table(type table[NumbersOnly = number],{{1.2},{8},{#date(2017,9,1)},{#date(2017,10,4)}}),
    #"Filtered Rows" = Table.SelectRows(Source, each not ([NumbersOnly] is date))
in
    #"Filtered Rows"

 

Alternatively you may be looking for a DAX solution, which is not my area of knowledge.

Specializing in Power Query Formula Language (M)

Thank you for the relies.

smoupre - the problem with this is the date is converted its associated number for example 03/03/2014 is converted to 41701 not an error so can't be filtered out.

 

MarcelBeug - thank you for the code, I tried to substitute the filter parts into my code...is this not DAX 🙂 real newbi sorry..however get various error messages. Hopefully below is my code would you be able to substitute the right bits? Just to clarify the column i'm trying to manipulate is typically  a numeric column containing quantities, but other erroneous data such as dates (could be any date) have been entered. I want to identify and replace the dates with Nulls ro remove them.

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Elaine\OneDrive\Documents\AP\TestData\Power BI Test - Orders.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Orders",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

 

From your code we can't see how your erroneous data look like and how it should be moved.

So please add a screenshot with represenrtative examples (numbers, dates, any other erroneous data).

 

Why do you first want to replace the values by null before removing them?
Or do you mean by "removing" that the values are set to null?

Specializing in Power Query Formula Language (M)

Date In TotalCost Needs RemovingDate In TotalCost Needs Removing

Please see screen shot snippet, I'm trying to remove the 03/03/2014 date

As you don't proviode all required information, I assume that you want to have all non-number values in column "TotalCost" replaced by null.

 

You can copy the red part from the query below and paste it - in the advanced editor - behind your last-line-before-"in", replacing your last part:

in

     #"Promoted Headers"

 

Notice that also the comma behind "Source"  is red and must be copied.

 

let
    Source = #table(type table[TotalCost = number],{{1.2},{8},{#date(2017,9,1)},{#date(2017,10,4)}}),
    #"Promoted Headers" = Source,
    NoNumberToNull = Table.TransformColumns(#"Promoted Headers", {{"TotalCost", each if not (_ is number) then null else _, type number}})
in
    NoNumberToNull

 

Specializing in Power Query Formula Language (M)

Once I inserted your code it work brilliantly, many thanks

Greg_Deckler
Super User
Super User

In the Query Editor, you can right-click the column and Remove Errors, etc. You can also click the little drop down arrow button and remove particular values.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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