Power BI. Replace "Blank" values with 0, but it's not null from Database

Question

I connect to Data from Stored Procedure not directly a table from Database so I'm not sure that's why I cannot use Script like

RiskCount = IF( ISBLANK( [My column] ), 0, [My column])

It's a warning cannot find name [My column]

My data from the database looks like

enter image description here

When data showed up that's made company 1 value c = "Blank"

I want to replace it with "0"

I'm not sure if Power BI can do something like this or if I should edit from the database?

Alexis Olson · Accepted Answer · 2019-06-18 15:15:33Z

1

The blanks in the column are likely an empty string "" rather than a null.

Try doing replacing values like @Aldert described, but leave the first box empty with 0 in the second box so you get M code like this:

= Table.ReplaceValue(Source,"",0,Replacer.ReplaceValue,{"Value"})

Notice the "" instead of null.

answered Jun 18, 2019 at 15:15

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JSBY · Accepted Answer · 2019-06-18 09:06:31Z

0

The best way to do this is in the Query editor. Press: Edit Queries.

Now select the table and column you want to change the values for and press: Replace Values.

You get a popup, first field null, second 0.

edited Jun 18, 2019 at 9:06

JSBY

13110 bronze badges

answered Jun 18, 2019 at 7:21

Aldert

4,3631 gold badge11 silver badges24 bronze badges

3 Comments

amy Over a year ago

i have to try this already. but it's not work cuz I don't have row that's have "Null" values

Aldert Over a year ago

So if your value is "Blank", you can put that in the first field. It can also be "", an empty string. Concept stays same, you need the replace function.

Maksim Khaitovich Over a year ago

Hi Albert. Sorry for follow up question, but is there a way to force PBI to import properly empty cells (with "") as Blanks? I have like 40 numeric columns in excel with some cells empty. I must import them as blanks (not zeros, it is important to distinguish valid zeros and blanks).

Punith · Accepted Answer · 2021-02-09 12:58:23Z

0

In the Power Query Editor(Advanced Editor)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{.........................
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,each _[Desired column for replacement],Replacer.ReplaceValue,{"Value"}),
in
    #"Replaced Value"

answered Feb 9, 2021 at 12:58

Punith

7024 gold badges12 silver badges34 bronze badges

Collectives™ on Stack Overflow

Power BI. Replace "Blank" values with 0, but it's not null from Database

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related