Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |