Where to clean #DIV/0, source or Power BI?

Power Query
1

Hi all, hope you’re doing well!

Is it best to clean errors like #DIV/0 (divide by zero) in the source or in Power Query? Is there a way in Power Query to take care of these types of errors in a scenario where there is no access to the source except via Power BI Desktop.

Thank you!

2

Hi @powerbideveloper,
Thank you very much for posting your query in the forum.
It is recommended to clean these errors at the source whenever possible.
If not, see if this post can help you handle it from power query.

Regards.

3

@jafernandezpuga,

Do you perhaps know if there is a solutions that doesn’t require a new column to be created?

4

Hi @powerbideveloper,
You could use the Table.ReplaceErrorValues function:

image
image1366×706 48.7 KB

image
image1364×710 47.7 KB

Regards

CleanDiv0_JAFP.pbix (386.5 KB)

5

@jafernandezpuga

You did the ReplaceValues manually correct? So, I am looking for a way to pick these types of issues up without having to manually do so. I am guessing that there should be a way to create a function/block of code that can be applied to all columns where these issues are automatically detected?

6

Hi @powerbideveloper,
I have created the following function that, starting from a table, replaces the errors in the columns of type Number.

let fnReplaceErrorsofNumberValues = (inputTable as table) as table =>
    let
        TableSchemaCall = Table.Schema(inputTable),
        FilteredRows = Table.SelectRows(TableSchemaCall, each ([TypeName] = "Number.Type")),
        ToList = FilteredRows[Name],
        SelectionOfColumns = Table.ColumnNames( Table.SelectColumns( inputTable, ToList ) ),
        #"Converted to Table" = Table.FromList(SelectionOfColumns, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
        #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each 0),
        #"Transposed Table" = Table.Transpose(#"Added Custom"),
        ColList = Table.ToColumns(#"Transposed Table"),
        #"Replaced Errors" = Table.ReplaceErrorValues(inputTable, ColList)
    in
        #"Replaced Errors",
      Documentation = [
      Documentation.Name =  " fxReplaceErrorsofNumberValues", 
      Documentation.Description = " Given a table, replaces errors in columns of type numeric ", 
      Documentation.LongDescription = " Given a table, replaces errors in columns of type numeric ", 
      Documentation.Category = " Table", 
      Documentation.Version = " 1.00: full code review",
      Documentation.Source = " local", 
      Documentation.Author = " José Antonio Fernández Puga"      
      ]
  in 
  Value.ReplaceType( fnReplaceErrorsofNumberValues, Value.ReplaceMetadata( Value.Type( fnReplaceErrorsofNumberValues ), Documentation ))

image
image1257×544 17.6 KB

We apply this function in the Data_CallfxReplaceErrorsValues query, after the step in which we assign the data types to the columns.

let
    Source = Excel.Workbook(File.Contents("C:\Enterprise DNA\Forum\Where to clean #DIV0, source or Power BI\Data.xlsx"), null, true),
    Tabla1_Table = Source{[Item="Tabla1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Tabla1_Table,{{"Numerator", Int64.Type}, {"Denominator", Int64.Type}, {"Result", type number}, {"Result2", type number}}),
    ReplaceErrors = fxReplaceErrorsofNumberValues(#"Changed Type")
in
    ReplaceErrors

image
image1257×543 30.5 KB

Regards,

Data.xlsx (9.7 KB)
CleanDiv0_JAFP.pbix (394.2 KB)

2 Likes