Forum Discussion

PC2790's avatar
PC2790
Community Champion
4 years ago

Comparing structured Lists with previous row

Hello Power Query experts,

 

I have hit a wall and have been struggling to get a solution.

So my requirement is to compare the list in a column with that of previous row and find out differences.

 

I tried creating an index column starting from 0 and one column from 1 but couldn't find a way out.

Can you help me achieve it?

If possible, can you help me with the comparios of structured table as well.

Attaching the file here for reference.

Need Help

5 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    If you can let us know the final result, we can rework the approach. I think approach needs a change here. 

    • PC2790's avatar
      PC2790
      Community Champion
      4 years ago

      Looaking for a solution like this:

      Month  Id  
      1/1/2021 1  
      1/1/2021 2  
      1/2/2021 1 3 added in this month
      1/2/2021 2 3 added in this month
      1/2/2021 3 3 added in this month
      1/3/2021 1 2 missing from this month
      1/3/2021 3 2 missing from this month
           
      • Vijay_A_Verma's avatar
        Vijay_A_Verma
        Most Valuable Professional
        4 years ago

        My apologies to come back on this. Can you please post the final result which you want? I went through your expanation but I am unable to conceptualize the final table which you need. If you can post the final result table, that would be great. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi PC2790 ,

     

    I see your idea is to group the dates, if you can, try grouping the IDs and then go and compare the changes in the dates. I'm sorry I've only done part of it, I'll spend more time researching the rest.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lEyVIrVQREwggoYoaswwqnCGCpgjK7FGElFLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Month " = _t, ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month ", type date}, {"ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Count", each _, type table [#"Month "=nullable date, ID=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index",1,1)),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Month ", "Index"}, {"Month ", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Count"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each if [Index]=1 then Text.From([ID])&" added in this month" else null),
    #"Sorted Rows" = Table.Sort(#"Added Custom1",{{"ID", Order.Ascending}, {"Month ", Order.Ascending}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows",{{"Month ", type date}}),
    #"Sorted Rows1" = Table.Sort(#"Changed Type1",{{"Month ", Order.Ascending}})
in
    #"Sorted Rows1"

     

     

     

    Best Regards,

    Stephen Tao

     

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

    • PC2790's avatar
      PC2790
      Community Champion
      4 years ago

      Thanks Anonymous for attempting.

      I appreciate the effort. However we are not quite there.

      Yes the idea is to group and find out the difference.

      I was able to figure out the added ones using the approach of grouping them and checking if the count is equal to 1. something like below:

      each Table.RowCount(_), Int64.Type}}),each [Count]=1))

      Still not able to figure out how to get the newly added ones.

      I really thought there would be a way of comparing structured lists and structured tables using some inbuilt function or even custom function.

      That would have been very helpful. I could not find it anywhere.