Replace the text values and some number values in a number field and do the same for a range of cols

[vkomarag]

Hi,

I have 300 fields in an excel. 260 columns has some ratings(1,2,3,4,5 as values). Unexpectedly there are values other than (1,2,3,4,5) and some text values in the fields. I need to replace the values other than 1,2,3,4,5 to blank value.

I need to do this for a range of 260 columns which are in sequential order.

Any pointers on this except R programming solution?

Thanks

Venkata

[Anonymous]

Does it need to be done in Power BI?  What is your data source?  Can you clean it up there?  If it was excel, i'd suggest a clean up macro.

[ImkeF]

This can easily be done in the query editor using this code:

let
    Source = YourTable,
    ChangeAllColumnsToText = Table.TransformColumnTypes(Source, List.Transform(Table.ColumnNames(Source), each {_, type text})),
    KeepOnlyOneToFives = Table.TransformColumns(ChangeAllColumnsToText,{}, (x) => Text.Combine(List.Select(Text.ToList(x), each List.Contains({"1", "2", "3", "4", "5"}, _)), ""))
in
    KeepOnlyOneToFives

 Just replace "YourTable" in the 1st Source-step by a reference to your table/query.

[vkomarag]

Thanks for your input. I got a way in case if i do anything in the future. For now, I am getting conversion error. I have unpivoted the column and converted into just one column called Rating.I have values(1,2,3,4,5,96,Yes,No)..Now i need to repalce all the values other than 1,2,3,4,5 to blank. Appreciate your response.

[ImkeF · Accepted solution]

If the 1-5-values you want to keep are real numbers, then this code will work (assuming you've kept the default-naming from the unpivot-operation "Value"):

let
    Source = YourTable,
    Keep = #table({"Keep"}, {{1},{2},{3},{4},{5}}),
    #"Merged Queries" = Table.NestedJoin(Source,{"Value"},Keep,{"Keep"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Keep"}, {"Keep"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"Value"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Keep", "Value"}})
in
    #"Renamed Columns"

[vkomarag]

Yeah got it. I have changed the Source to my previous column in the Merged Queries step and it worked out. Thanks a lot.

You are genius !  We are suggesting this tool for our business to do their own development activities. I am just wondering how could they code this kind of things all the times.

Anyway. Your excellence helped a lot.

[vkomarag]

After unpivoting, i didnt change the column name. by default it is " Value". After using your code,an error occured saying that 

" The column 'Value' of the table was not found"

and the Merged queries step got highlighted.