Reply
Frequent Visitor
Posts: 9
Registered: ‎01-04-2017

Comaring data based on date

Hello, I am having power bi data set which is having data on different dates. I want to add a "Status" cloumn to check if resource exists in previous date. If exists in previous date i need add "Existing", if not exists in current date "Deleted", If exists only in current date but not in previous date "New".

 

At the end I need to generate a report to identfy list of resources which are added/deleted/existing. Any inputs on how to do this?

 

DateResourceAmount
Mar-18CLS110
Mar-18CLS220
Mar-19CLS110
Mar-19CLS315
Mar-20CLS112
Mar-20CLS312
Mar-20CLS412

 

DateResourceAmountStatus
Mar-18CLS110Existing
Mar-18CLS220Deleted
Mar-19CLS110Existing
Mar-19CLS315New
Mar-20CLS112Existing
Mar-20CLS312Existing
Mar-20CLS412New
New Contributor
Posts: 622
Registered: ‎11-25-2016

Re: Comaring data based on date

First I would like to remark that it is a bit strange to have "Deleted" on the last day of the resource. I would expect an additional row with the next date for that resource and status "Deleted".

Now you have an issue with 1 day resources: are they "New" or "Deleted"? In my solution below it will be "New".

In my solution, a resource will not be marked "New" on the first date that appears in the file (i.c. March 18); likewise a resource will not be marked as "Deleted" on the last date that appears in the file.

The logic for determining the status is: check for "New" then check for "Deleted" and what remains is "Existing".

 

Now the logic of the query:

 

In general if you need data from different rows, it's most efficient to get all the data you need on 1 row.

You can watch the first minute of this video (I created for another question) to see how you get data from the previous row on the current row. I applied the same technique for you (and similarly to get data from the next row on the current row).

 

  1. The file is imported in Power Query (i.c. from the current Excel workbook) and automatically typed.
    I adjusted type datetime to date.
  2. I first added an Index as Original sort so the results will be sorted back to the original sort at the end.
  3. Sort on resource and date.
  4. Now you need data from the previous and the next row.
    In order to get the data on the same row, I added 2 indices, starting with 0 and 1 respectively.
    Next the table is merged with itself twice: first joined on the 0-Index and 1-Index to get the data from the previous row,
    then on the 1-index and 0-index to get the data from the next row.
  5. After each merge code was created, I adjusted the code and changed "NewColumn" in "Previous" resp. "Next"
  6. After the merges you have 2 columns with nested tables that need to be expanded.
    Only the Resource is required. With expansion, keep the "Use original column name as prefix" checked,
    so you get Previous.Resource and Next.Resource.
  7. I added 2 lines in the advanced editor to determine the min and max date in the file.
  8. I added a column with the Status according to the logic I outlined above.
  9. As finishing touches I sorted back to the original sort and removed all helper columns.
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Resource", type text}, {"Amount", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Original Sort", 1, 1),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Resource", Order.Ascending}, {"Date", Order.Ascending}}),
    #"Added Index1" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
    #"Added Index2" = Table.AddIndexColumn(#"Added Index1", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index2",{"Index"},#"Added Index2",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Merged Queries1" = Table.NestedJoin(#"Merged Queries",{"Index.1"},#"Merged Queries",{"Index"},"Next",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries1", "Previous", {"Resource"}, {"Previous.Resource"}),
    #"Expanded Next" = Table.ExpandTableColumn(#"Expanded Previous", "Next", {"Resource"}, {"Next.Resource"}),
    MinDate = List.Min(#"Sorted Rows"[Date]),
    MaxDate = List.Max(#"Sorted Rows"[Date]),
    #"Added Custom" = Table.AddColumn(#"Expanded Next", "Status", each if [Resource] <> [Previous.Resource] and [Date] <> MinDate then "New" else if [Resource] <> [Next.Resource] and [Date] <> MaxDate then "Deleted" else "Existing"),
    #"Sorted Rows1" = Table.Sort(#"Added Custom",{{"Original Sort", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"Original Sort", "Index", "Index.1", "Previous.Resource", "Next.Resource"})
in
    #"Removed Columns"
Super Contributor
Posts: 1,291
Registered: ‎11-29-2015

Re: Comaring data based on date

Hi @mrvamsidhar

 

This is a DAX approach which I think might be close but I don't fully understand your requirements.  Your definition says one thing but your test data doesn't match that.

 

Status Column = 
var ResourceColumn = 'test'[Resource]
var DateColumn = test[Date]
RETURN SWITCH(
    CALCULATE(
            COUNTROWS('test'),
            Filter('test',
                'test'[Resource]= ResourceColumn 
                && 'test'[Date] < DateColumn
                )
               ),blank(),"New","Existing")