Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kimia79
Frequent Visitor

Change over time

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

respondent_idsurvey_ownersurvey_idCustomer_id7020 - How happy were you with your product7022 - Would you recommend this store?Date
049ea99f8337255e14be694bcaa8390610ce192547f42a53694b47f4225e978/09/2022
0983db1ad78722e414be694bcaa8390610ce192547f42a53d5066ew3h4qh1086/01/2023
0d0afeb3202db0ff14be694bcaa8390610ce192547f42a53isiioj8n37dk6517/03/2023
1349f198024d2c2d14be694bcaa8390610ce192547f42a53mlql9s1ga2hi774/04/2023
1be1f5243cf1b72714be694bcaa8390610ce192547f42a53sbkrqn27bech777/02/2023
1e3f6faa888922fb14be694bcaa8390610ce192547f42a53d5066ew3h4qh8817/02/2023
1f45294b042838f314be694bcaa8390610ce192547f42a53isiioj8n37dk774/04/2023
3 REPLIES 3
tackytechtom
Super User
Super User

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! linkedIn

#proudtobeasuperuser 

tackytechtom
Super User
Super User

Hi @Kimia79 ,

 

Here a solution in Power Query as requested:

tackytechtom_0-1684381124540.png

 

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! 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?


Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors