March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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 |
Hi @Kimia79 ,
Here the file
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
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/
Did I answer your question❓➡️ Please, mark my post as a solution ✔️ |
Also happily accepting Kudos 🙂 |
Feel free to connect with me on LinkedIn! |
#proudtobeasuperuser |
Thanks tackytechtom,
thanks for your responce
Can you upload the file, then I can figure out the steps and applied with my data?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
20 | |
12 | |
10 | |
10 | |
7 |
User | Count |
---|---|
42 | |
25 | |
16 | |
16 | |
11 |