Change over time

[Kimia79]

Hi,
I need to calculate change over time for any customer that comes back.

respondent_id	survey_owner	survey_id	Customer_id	7020 - How happy were you with your product	7022 - Would you recommend this store?	Date
049ea99f8337255e	14be694bcaa83906	10ce192547f42a53	694b47f4225e	9	7	8/09/2022
0983db1ad78722e4	14be694bcaa83906	10ce192547f42a53	d5066ew3h4qh	10	8	6/01/2023
0d0afeb3202db0ff	14be694bcaa83906	10ce192547f42a53	isiioj8n37dk	6	5	17/03/2023
1349f198024d2c2d	14be694bcaa83906	10ce192547f42a53	mlql9s1ga2hi	7	7	4/04/2023
1be1f5243cf1b727	14be694bcaa83906	10ce192547f42a53	sbkrqn27bech	7	7	7/02/2023
1e3f6faa888922fb	14be694bcaa83906	10ce192547f42a53	d5066ew3h4qh	8	8	17/02/2023
1f45294b042838f3	14be694bcaa83906	10ce192547f42a53	isiioj8n37dk	7	7	4/04/2023

[tackytechtom]

Hi @Kimia79 ,

Here the file

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[tackytechtom]

Hi @Kimia79 ,

Here a solution in Power Query as requested:

Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndDBbsMgDAbgd8m5Uoxtgv0sVQ8Q8JK165TmsNcfZJUWTTtEPfwHLPOB/vO5A9YSVU2IAnpfulPnOJVBOY0xCikMbQRjcYqegzFGT3XUNrYjbpe0JtRID9ojIHaXU9VVKCcXc5CAWPiwnj0MQ/miiZdp22h0e7UH13j64TNEK4nqICcwO8zP6zx/vsudQr42tca3zdAD/fKOWM2pAHLGEfNh/uO23HR1bxGn+dlLC/fAOz0VZx6ZRnMpYDisr+n6WO4YUhmnnV7/jju9kA1WHRFFtPRq8/KM+8Mbe6wWMAqJ0avN/1PN5Rs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [respondent_id = _t, survey_owner = _t, survey_id = _t, Customer_id = _t, #"7020 - How happy were you with your product" = _t, #"7022 - Would you recommend this store?" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"respondent_id", type text}, {"survey_owner", type text}, {"survey_id", type text}, {"Customer_id", type text}, {"7020 - How happy were you with your product", Int64.Type}, {"7022 - Would you recommend this store?", Int64.Type}, {"Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Customer_id"}, {{"Allrows", each GroupStuff(_), type table [respondent_id=nullable text, survey_owner=nullable text, survey_id=nullable text, Customer_id=nullable text, #"7020 - How happy were you with your product"=nullable number, #"7022 - Would you recommend this store?"=nullable number, Date=nullable date]}}),
    GroupStuff = (Table as table) as table =>
    let
        #"Added Index" = Table.AddIndexColumn(Table, "Index1", 0, 1, Int64.Type),
        #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index2", 1, 1, Int64.Type),
        #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index1"}, #"Added Index1", {"Index2"}, "Added Index1", JoinKind.LeftOuter),
        #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"7020 - How happy were you with your product", "7022 - Would you recommend this store?"}, {"Added Index1.7020 - How happy were you with your product", "Added Index1.7022 - Would you recommend this store?"}),
        #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "Change 7020 - How happy were you with your product", each [#"7020 - How happy were you with your product"] - [#"Added Index1.7020 - How happy were you with your product"]),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Change 7022 - Would you recommend this store", each [#"7022 - Would you recommend this store?"] - [#"Added Index1.7022 - Would you recommend this store?"]),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"respondent_id", "survey_owner", "survey_id", "Customer_id", "7020 - How happy were you with your product", "7022 - Would you recommend this store?", "Date", "Change 7020 - How happy were you with your product", "Change 7022 - Would you recommend this store"})
    in
        #"Removed Other Columns",
#"Expanded Groups" = Table.ExpandTableColumn(#"Grouped Rows", "Allrows", {"respondent_id", "survey_owner", "survey_id", "7020 - How happy were you with your product", "7022 - Would you recommend this store?", "Date", "Change 7020 - How happy were you with your product", "Change 7022 - Would you recommend this store"}, {"respondent_id", "survey_owner", "survey_id", "7020 - How happy were you with your product", "7022 - Would you recommend this store?", "Date", "Change 7020 - How happy were you with your product", "Change 7022 - Would you recommend this store"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Groups",{"respondent_id", "survey_owner", "Customer_id", "survey_id", "7020 - How happy were you with your product", "7022 - Would you recommend this store?", "Date", "Change 7020 - How happy were you with your product", "Change 7022 - Would you recommend this store"}) 
in     
    #"Reordered Columns"

Note, this is something I'd probably done in a DAX measure instead of Power Query. But since you posted it here, I gave it a shot in PQ anyway.

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[Kimia79]

 Thanks tackytechtom,
thanks for your responce
Can you upload the file, then I can figure out the steps and applied with my data?