Gathering name of products which have a same discount percentage

    Hello,

    I am preparing personal excel sheet, and need to has a feature of showing all products which were inputted, which were got same discount % by visiting retail shop websites for easy reference and make it more convince outputs data to me.

    Thanks and regards,

    Please provide a sample workbook (10-15 records) showing your current data and then a mocked up solution of what you expect. Do not post a picture as we cannot manipulate data in a picture. Attach an excel file.

    With Power Query and a Group By function, here is the Mcode

    Code
    let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"discount %"}, {{"Data", each _, type table [product name=number, #"discount %"=number, attracting level=text]}}),
#"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"product name", "attracting level"}, {"product name", "attracting level"}),
#"Sorted Rows" = Table.Sort(#"Expanded Data",{{"discount %", Order.Ascending}})
in
#"Sorted Rows"

    Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").



    It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.



    - Follow this link to learn how to install Power Query in Excel 2010 / 2013.



    - Follow this link for an introduction to Power Query functionality.



    - Follow this link for a video which demonstrates how to use Power Query code provided.

    Tried use the earlier code, unfortunately I got an error as per the attached 1st snapshot; so I did replaced "Second Line" to be: Source = Excel.Workbook(File.Contents("C:\Users\xyz\Downloads\Book1.xlsx"), null, true), afterward the code remains cant run correctly there's a new error message according to 2nd snapshot.

    Thanks and regards,





    Pictures cannot be manipulated. Attach a file with your Mcode that is not working.


    Second Look. Maybe your spacing on the header for discount % is not exactly as it seems. Make sure that there are no leading or trailing spaces. But to be sure, read my first sentence above.

    Hello,


    Sorry for delay, please note that these 2 codes are not working as per earlier post.

    thanks and regards,



    let

    Source = Excel.Workbook(File.Contents("C:\Users\XYZ\Downloads\Book1.xlsx"), null, true),

    #"Grouped Rows" = Table.Group(Source, {"discount %"}, {{"Data", each _, type table [product name=number, #"discount %"=number, attracting level=text]}}),

    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"product name", "attracting level"}, {"product name", "attracting level"}),

    #"Sorted Rows" = Table.Sort(#"Expanded Data",{{"discount %", Order.Ascending}})

    in

    #"Sorted Rows"



    let

    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    #"Grouped Rows" = Table.Group(Source, {"discount %"}, {{"Data", each _, type table [product name=number, #"discount %"=number, attracting level=text]}}),

    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"product name", "attracting level"}, {"product name", "attracting level"}),

    #"Sorted Rows" = Table.Sort(#"Expanded Data",{{"discount %", Order.Ascending}})

    in

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login