Forum Discussion

Dicken's avatar
Dicken
Post Prodigy
1 year ago
Solved

Power Query ; Table Replace Error problem


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.

Need Help
Show and Tell
  • v-saisrao-msft's avatar
    v-saisrao-msft
    1 year ago

    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.

3 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    1 year ago

    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
  • v-saisrao-msft's avatar
    v-saisrao-msft
    Community Support
    1 year ago

    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's avatar
      Dicken
      Post Prodigy
      1 year ago

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