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 difference...
Need Help
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.