replace error

[cosminc]

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

[Greg_Deckler]

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]

ok thanks

now i have almost 1500 columns which farther there are unpivoted

an automatically solution would be wonderfull

[ImkeF]

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]

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

[ImkeF · Accepted solution]

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

[cosminc]

thanks,

there was another one 

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