Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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
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.
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?
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
Once I inserted your code it work brilliantly, many thanks
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.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |