Needed help with removing blank values

[Johajong]

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!!

[Johajong]

@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]

@Johajong 

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 y

if [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 🙂

YouTube  LinkedIn

[Fowmy · Accepted solution]

@Johajong 
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 🙂

YouTube  LinkedIn

[Johajong]

@Fowmy, thanks, but I am not used working with GUI... Is there an easier solution??

[Fowmy]

@Johajong 

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 🙂

YouTube  LinkedIn

[AlB]

Hi @Johajong 

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 

[Johajong]

@AlB , I have sample data uploaded in the first post.