Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Remove rows based on whether there are values for the material in other rows

Hello,

 

Within Power Query I'm looking for help with how to remove rows or at least flag rows for removal based on whether rows with the same product id but different size have a value or not. I have thousands of materials and would like to remove those rows whose total inventory is 0 across all sizes. However if any material has sizes with or without inventory then keep all the rows related to the material. See example below.

 

Any help would be appreciated. Thanks

 

 

 
Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Anonymous 

     

    Connect yo your CSV file as normal and paste the below after #"Change Type" step

     #"Grouped Rows" = Table.Group(#"Changed Type", {"Material"}, {{"tbl", each _, type table [Material=text, Size=number, Inventory=number]}, {"Inv", each List.Sum([Inventory]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each not ([Inv] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Inv"}),
    #"Expanded tbl" = Table.ExpandTableColumn(#"Removed Columns", "tbl", {"Size", "Inventory"}, {"tbl.Size", "tbl.Inventory"})
in
    #"Expanded tbl"

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi Anonymous 

     

    Please see the below M expression, please see the Applied Steps once in the query editor.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKTylNLjFU0lEysgASBkqxOqiilthEjQ2wihpiFTXCKmqMVdQEq6gZVlGs7jXBcJkRVr8ZYfWbEcxvhhii4MDAEDXCKmqM1QQTrGpBfjPGEMXqXpjfYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Material = _t, Size = _t, Inventory = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Material", type text}, {"Size", Int64.Type}, {"Inventory", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Material"}, {{"tbl", each _, type table [Material=text, Size=number, Inventory=number]}, {"Inv", each List.Sum([Inventory]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each not ([Inv] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Inv"}),
    #"Expanded tbl" = Table.ExpandTableColumn(#"Removed Columns", "tbl", {"Size", "Inventory"}, {"tbl.Size", "tbl.Inventory"})
in
    #"Expanded tbl"
    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Mariusz ,

       

      Thanks for the help. I'm getting a "Token Identifier expected" error with the second "let" when I attempt a test with the expression you provided. The data source is a txt file.

      Is there a way around that error?

       

      Thanks

      Macbar

      • Mariusz's avatar
        Mariusz
        Community Champion
        6 years ago

        Hi Anonymous 

         

        Connect yo your CSV file as normal and paste the below after #"Change Type" step

         #"Grouped Rows" = Table.Group(#"Changed Type", {"Material"}, {{"tbl", each _, type table [Material=text, Size=number, Inventory=number]}, {"Inv", each List.Sum([Inventory]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each not ([Inv] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Inv"}),
    #"Expanded tbl" = Table.ExpandTableColumn(#"Removed Columns", "tbl", {"Size", "Inventory"}, {"tbl.Size", "tbl.Inventory"})
in
    #"Expanded tbl"

         

        Best Regards,
        Mariusz

        If this post helps, then please consider Accepting it as the solution.