Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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.
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.