Forum Discussion

mudegowdrp's avatar
mudegowdrp
Icon for Helper II rankHelper II
8 years ago
Solved

Compare 2 columns from 2 different sheets

I have data from week 1 and week 2.I need to find new items in week 2 detected. Please can I know best way to do this.   Ex: Attached file has two columns ID and AppName. What I need is list of new...
  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago
    mudegowdrp wrote:

    Phil_Seamark Ok here is the dataset.

     

    Week - 1

         Date                |         ID           | ApplicationName

    01/11/2017                     1               a

    01/11/2017                     2               b

    01/11/2017                     3               c

    01/11/2017                     4               d

     

    Week - 2

     

    Date                |         ID           | ApplicationName

    08/11/2017                     1               a

    08/11/2017                     2               b

    08/11/2017                     3               c

    08/11/2017                     4               d

    08/11/2017                     5               e

    08/11/2017                     6               f

    08/11/2017                     7               g

    08/11/2017                     8               h

     

    Result should look like

     

    New Items: 4

    5        e

    6        f

    7        g

    8        h

     

    Note: Week 1 and Week 2 data is stored in two different excel sheets

     

    mudegowdrp

    Create a calculated column which can be used in filters/slicers.

    isNew =
NOT (
    CONTAINS (
        week1,
        week1[ID], week2[ID],
        week1[ApplicationName], week2[ApplicationName]
    )
)

     

     

     

     

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi mudegowdrp

 

Do you need an Excel based solution, or are you bringing this data into PowerBI?

  • mudegowdrp's avatar
    mudegowdrp
    Icon for Helper II rankHelper II
    8 years ago

    Hi Phil_Seamark,

     

    Thank you for your quick reply.

     

    I need to bring this in Power BI. I need to compare weekly data and show that how many new applications came into environment.

     

     

    • Phil_Seamark's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      So will you bring in both sets of data into the same table in Power BI?  Perhaps with a column showing the week the row relates to?

       

      If so, you can create either a calculated column or measure to identify which values are new

      • mudegowdrp's avatar
        mudegowdrp
        Icon for Helper II rankHelper II
        8 years ago

        That's a good question. Trying to think what is the best way to do that. What I will be doing is, I'll be exporting data every week and I really dont want to keep everything in one sheet as the volumne of the data is more. But can you give better idea to do this?