Forum Discussion

LoxleyLearner's avatar
LoxleyLearner
New Member
5 years ago
Solved

Delete rows - based on one blank column only

I'm looking at the BP world energy report and have the tall thin dataset downloaded.  It uses ISO3 values (great) but in the ISO3 column has blanks for regional summaries (not so great as these sum to a huge value).  How can I use powerbi to remove all rows based on only the blank status for the ISO3 column?  I can achieve this by editing the file in EXCEL of course, but this seems ... well ... a bit antideluvian.  I could also simple remove all top rows, but this is a big dataset and 5 minutes of paging down did not get me past the blanks.

 

This question has been addressed elsewhere, but it seems to only refer to removing rows where all cells are blank and this is not getting me what I want.  Thanks for your help

Need Help
  • v-lionel-msft's avatar
    v-lionel-msft
    5 years ago

    Hi LoxleyLearner ,

     

    Is it like this?

    Please try this M code.

    let
    Source = Excel.Workbook(File.Contents("C:\Users\lionelch\Desktop\Sampledata\Data\content\Chapter_1\2015 Sales.xlsx"), null, true),
    Sales_Table = Source{[Item="Sales",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sales_Table,{{"CountryRegion", type text}, {"Brand", type text}, {"Month", type text}, {"Sale 2013", type any}, {"Sale 2014", type number}, {"Sale 2015", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(
        #"Changed Type", 
        each 
        ([CountryRegion] <> null and [CountryRegion] <> "") and
        ([Brand] <> null and [Brand] <> "") and
        ([Month] <> null and [Month] <> "") and
        ([Sale 2013] <> null and [Sale 2013] <> "") and
        ([Sale 2014] <> null and [Sale 2014] <> "") and
        ([Sale 2015] <> null and [Sale 2015] <> "")
    )
in
    #"Filtered Rows"

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

4 Replies

    • LoxleyLearner's avatar
      LoxleyLearner
      New Member
      5 years ago

      I can see how I can do that when creating the visualisation (a bubble map selecting filter removing null etc) but this has performance implications importing 40,000 not needed rows.  How do I do this in data transformation so that I right size my dataset on import?

  • v-lionel-msft's avatar
    v-lionel-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi LoxleyLearner ,

     

    Is it like this?

    Please try this M code.

    let
    Source = Excel.Workbook(File.Contents("C:\Users\lionelch\Desktop\Sampledata\Data\content\Chapter_1\2015 Sales.xlsx"), null, true),
    Sales_Table = Source{[Item="Sales",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sales_Table,{{"CountryRegion", type text}, {"Brand", type text}, {"Month", type text}, {"Sale 2013", type any}, {"Sale 2014", type number}, {"Sale 2015", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(
        #"Changed Type", 
        each 
        ([CountryRegion] <> null and [CountryRegion] <> "") and
        ([Brand] <> null and [Brand] <> "") and
        ([Month] <> null and [Month] <> "") and
        ([Sale 2013] <> null and [Sale 2013] <> "") and
        ([Sale 2014] <> null and [Sale 2014] <> "") and
        ([Sale 2015] <> null and [Sale 2015] <> "")
    )
in
    #"Filtered Rows"

     

    Best regards,
    Lionel Chen

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.