Forum Discussion
Needed help with removing blank values
Hi all,
I am editing data in the query editor and I want to remove the blank values in the Function column, but not in case the blank value is the only row for a specific Module. How to tackle this one?
| Module | Function | |
| Global Sailing Schedules | stay | |
| Global Flight Schedules | stay | |
| Reports | must be removed | |
| Reports | Customize Reports | |
| Reports | Run Reports | |
| Reports | Run Reports -> Schedule | |
| Reports | Run Reports -> Sea Freight - Route Planning | |
| Reports | Run Reports -> 1-Stop Vessel Arrival Report | |
| Reports | Run Reports -> FCL Container Availability | |
| Confirmations | must be removed | |
| Confirmations | Edit | |
| Confirmations | New | |
| Confirmations | Delete |
Thanks!!
Anonymous
Paste the below code on in the Advanced Editor on a Blank query and check the steps, All done using GUI.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUVKK1UFiJCEzKuCsYjgrFc4qArOSYRpSkBklSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Module = _t, Function = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Function"}), #"Grouped Rows" = Table.Group(#"Replaced Value", {"Module"}, {{"Count", each List.Max([Function]), type nullable text}, {"all", each _, type table [Module=nullable text, Function=nullable text]}}), #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Module", "Function"}, {"Module.1", "Function"}), #"Added Custom" = Table.AddColumn(#"Expanded all", "Custom", each [Count] <> null and [Function]=null), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = false)), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Module.1", "Function"}) in #"Removed Other Columns"________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
7 Replies
- Fowmy
Super User
Anonymous
Paste the below code on in the Advanced Editor on a Blank query and check the steps, All done using GUI.let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUVKK1UFiJCEzKuCsYjgrFc4qArOSYRpSkBklSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Module = _t, Function = _t]), #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Function"}), #"Grouped Rows" = Table.Group(#"Replaced Value", {"Module"}, {{"Count", each List.Max([Function]), type nullable text}, {"all", each _, type table [Module=nullable text, Function=nullable text]}}), #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Module", "Function"}, {"Module.1", "Function"}), #"Added Custom" = Table.AddColumn(#"Expanded all", "Custom", each [Count] <> null and [Function]=null), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = false)), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Module.1", "Function"}) in #"Removed Other Columns"________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
Fowmy, thanks, but I am not used working with GUI... Is there an easier solution??
- Fowmy
Anonymous
In Power Query, Click Get Data, Choose Blank Query > Go to Advanced Editor in View Menu > Clear all that is there and paste my code.
Now you can check the steps on How I solved it.________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
- AlB
Community Champion
Hi Anonymous
Can you share some sample data in text-tabular format (instead of pic) so that we can run a quick test and show the steps?
I assume the green marks are the rows that stay and the red one the rows that are to be removed, correct?
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
AlB , I have sample data uploaded in the first post.
Fowmy thanks for your solution, it worked! I have one follow up question, how can I replace "null" in the Function columns for only the FALSE values in the Custom column??
- Fowmy
Anonymous
You can add a custom column: click Add Column Tab > Custom and use the below code. you can select any other column or any preferred value for x and yif [Function] = null and [Custom] = false then "x" else "y"________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
