Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 t...
Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    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 🙂

    YouTube  LinkedIn







Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
Fowmy
Icon for Super User rankSuper User
5 years ago

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