Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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/
Proud to be a Super User!
Hi @Kimia79 ,
Here a solution in Power Query as requested:
Here the code in power query's M. Just paste the code into the advanced editor. Feel free to look into the applied steps to figure out what the code does:
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/
Proud to be a Super User!
Thanks tackytechtom,
thanks for your responce
Can you upload the file, then I can figure out the steps and applied with my data?
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.