CODEWITHSUNDEEP
powerbidaxpowerquerym
amy
amy

Reputation: 1

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

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?

Upvotes: 0

Views: 14551

Answers (3)

Punith Gubbi
Punith Gubbi

Reputation: 692

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"

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40264

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.

Upvotes: 1

Aldert
Aldert

Reputation: 4323

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

enter image description here

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

enter image description here

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

Upvotes: 0

Related Questions