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 ...
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
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}))
      • cosminc's avatar
        cosminc
        Icon for Post Partisan rankPost Partisan
        7 years ago

        Hi,

        can you help me with the right syntax?

        i miss something and don't khow what.

        Thanks a lot!

        Cosmin

         

        ps: the columns are not yet put on text or number format

         

        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