cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Johajong
Frequent Visitor

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?

 

ModuleFunction 
Global Sailing Schedules stay
Global Flight Schedules stay
Reports must be removed
ReportsCustomize Reports 
ReportsRun Reports 
ReportsRun Reports -> Schedule 
ReportsRun Reports -> Sea Freight - Route Planning 
ReportsRun Reports -> 1-Stop Vessel Arrival Report 
ReportsRun Reports -> FCL Container Availability 
Confirmations must be removed
ConfirmationsEdit 
ConfirmationsNew 
ConfirmationsDelete 

 

Thanks!!

1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

@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







Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

7 REPLIES 7
Johajong
Frequent Visitor

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

 

Johajong_0-1604583360361.png

 

@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





Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Fowmy
Super User
Super User

@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







Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Johajong
Frequent Visitor

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

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

AlB
Super User
Super User

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 

SU18_powerbi_badge

Johajong
Frequent Visitor

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors