Power Query ; Table Replace Error problem

[Dicken]

Hi, 
I have to questions regarding this,      one why doesn't Table.ReplaceErrors work and how can I make it work? 

 let
Source = #table(type table [A = text, B = text], {{"a", "b"}, {"a", "c"}, {"d", "f"}, {"c", "d"}}),
reclist = List.InsertRange(Table.ToRecords(Source), 2, {"", ""}),
totable = Table.FromRecords(reclist),
replace = Table.ReplaceErrorValues(totable, {{"A", ""}, {"B", ""}})
in
replace

Richard.

[v-saisrao-msft · Accepted solution]

Hi @Dicken,

Thank you for reaching out to the Microsoft Fabric Forum community.

I’ve reproduced the scenario using your sample data and for your reference, I’m attaching the output screenshot and the .pbix file. Thanks, @dufoq3 for providing your insights.

let Source = #table(type table [A = text, B = text], {{"a", "b"}, {"a", "c"}, {"d", "f"}, {"c", "d"}}), reclist = List.InsertRange(Table.ToRecords(Source), 2, {"", ""}), Transformed = List.Transform(reclist, each if Value.Is(_, Record.Type) then _ else [A = null, B = null]), ToTbl = Table.FromRecords(Transformed, null, MissingField.UseNull), 

ReplaceNulls = Table.ReplaceValue(ToTbl, null, "", Replacer.ReplaceValue, {"A", "B"}) 
 

in ReplaceNulls 

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

[Dicken]

Thanks, are either of you able to eplain why Table.Replace Errors does not reocognise what is an error,
and just replace them ? 

[dufoq3]

Hi @Dicken, check this:

let
    Source = #table(type table [A = text, B = text], {{"a", "b"}, {"a", "c"}, {"d", "f"}, {"c", "d"}}),
    reclist = List.InsertRange(Table.ToRecords(Source), 2, {"", ""}),
    Transformed = List.Transform(reclist, each if Value.Is(_, Record.Type) then _ else []),
    ToTbl = Table.FromRecords(Transformed, null, MissingField.UseNull)
in
    ToTbl