Forum Discussion

cosminc's avatar
cosminc
Icon for Post Partisan rankPost Partisan
7 years ago
Solved

replace error

Hi all,

i have a data base (excel) in which i need to replace errors with 0 - all base;

on future refreshments the data base will have new columns and i need now to set up this replacement for all base variable; otherwise for future columns the issue will remain.

Thanks!

Cosmin

Need Help
  • ImkeF's avatar
    ImkeF
    7 years ago

    I guess there is just a tiny "#" missing in step "Table_without_error": 

     

    let
Source = Excel.Workbook(File.Contents("C:\Users\coscirnu\Desktop\lucru\Brand_Tracker\Sursa\All_Categories_All_Waves_Labels.xlsx"), null, true),
All_Categories_All_Waves_Labels_Sheet = Source{[Item="All_Categories_All_Waves_Labels",Kind="Sheet"]}[Data],
Table_without_error = Table.ReplaceErrorValues(#"All_Categories_All_Waves_Labels_Sheet", List.Transform(Table.ColumnNames("All_Categories_All_Waves_Labels_Sheet"), each {_, 0}))
in
Table_without_error

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    You can use the replace errors functionality of the Query Editor to replace errors with 0. For future columns, that's tricky, perhaps ImkeF has a suggestion for that.

    • cosminc's avatar
      cosminc
      Icon for Post Partisan rankPost Partisan
      7 years ago

      ok thanks

      now i have almost 1500 columns which farther there are unpivoted

      an automatically solution would be wonderfull

      • ImkeF's avatar
        ImkeF
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Hi cosminc ,

        a dynamic solution would be to add a step with with formula:

         

        Table.ReplaceErrorValues(<YourTableName>, List.Transform(Table.ColumnNames(<YourTableName>), each {_, 0}))