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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Samhunt
Helper II
Helper II

Replace Error

Hi guys,

 

Is it possible to replace errors in a column in  power query with the value from another column in the same table?

 

Screenshot 2023-12-30 121142.png

 

So for example in my case I want every column's error value replaced with the value in the general rating column. 

 

Regards

 

1 ACCEPTED SOLUTION
Samhunt
Helper II
Helper II

Thank you very much for your replies guys, 

 

What I did was the following, 

Replace error with null, 

 

Then I replaced null with a numerical value that was higher than any other possible value in the column. 

In my case the values were from 1-10 so the value I added was 110

 

Then I went to the code and replaced 110 with: each [General rating]

 

Thanks for your time, 

 

Have a lovely new year

View solution in original post

5 REPLIES 5
Samhunt
Helper II
Helper II

Thank you very much for your replies guys, 

 

What I did was the following, 

Replace error with null, 

 

Then I replaced null with a numerical value that was higher than any other possible value in the column. 

In my case the values were from 1-10 so the value I added was 110

 

Then I went to the code and replaced 110 with: each [General rating]

 

Thanks for your time, 

 

Have a lovely new year

Anonymous
Not applicable

Hi @Samhunt 

You can refer to the following steps

1.Replace the errors with null

vxinruzhumsft_0-1704073826256.png

 

vxinruzhumsft_1-1704073877739.png

2.Replace the null to the general rating value

Table.ReplaceValue(#"Renamed Columns1",each [Service],each if [Service]=null then [General rating] else [Service],Replacer.ReplaceValue,{"Service"})

You can also refer to the following code to advanced editor in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSAeNEpVidaCUjIAuEk8C8RCDLBCwP4oFYpmAcGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Column1", null}}),
    #"Replaced Errors1" = Table.ReplaceErrorValues(#"Replaced Errors", {{"Column3", null}}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Errors1",{{"Column2", "General rating"}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{"Column3", "Service"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns1",each [Service],each if [Service]=null then [General rating] else [Service],Replacer.ReplaceValue,{"Service"})
in
    #"Replaced Value"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

m_dekorte
Super User
Super User

Hi @Samhunt ,

 

You can give this custom replError function a go.

It takes 2 argument values, first the table to transform and second the column name that contains the value to replace any errors by. As illustrated in this query.

 

 

let
  Source = Table.FromColumns(
    {{1 .. 9}, {"A" .. "E", 1 & 2, "G" .. "I"}, {"a" .. "e", 1 & 2, "g" .. "i"}}
  ), 
  replError = (tbl as table, replByCol as text) =>
    Table.FromRecords(
      Table.TransformRows(
        tbl, 
        each Record.FromTable(
            Table.ReplaceErrorValues(Record.ToTable(_), {"Value", Record.Field(_, replByCol)})
          )
      )
    ), 
  t = replError(Source, "Column1")
in
  t

 

 

 

here's the input table

m_dekorte_0-1704026156976.png

 

and the return value, after invoking the custom function

m_dekorte_2-1704026196852.png

 

I hope this is helpful.

PijushRoy
Super User
Super User

Hi @Samhunt 

You are getting error after change type to Whole Number.
Can you please share screenshot of the table, before change datatype?




Did I answer your question? Mark my post as a solution!
Appreciate your Like/Kudos

Proud to be a Super User!





zenisekd
Super User
Super User

@Samhunt , use this procedure, only instead of replace value use replace error:
https://wmfexcel.com/2021/09/04/how-to-replace-value-with-a-value-from-another-column-in-power-query...

 

Kudos and mark as solution appreciated.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.