Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Team,
1. I have below end user behavior data in several http sessions. I want to transform them into a format that Sankey Diagram supported: Source, Target and Weight.
2. My expected data flow in the Sankey Diagram is userId->httpsessionid->module->page->actionType1->actionType2->actionType3 ... (the number of actiontypes depends on how many actions the end user perform in a http session. )
3. Now I have difficulties to finish this data transformation by power query, Anyone can help how many steps need I do to achieve this?
when,httpsessionId,module,page,userId,actionType
Solved! Go to Solution.
Hi @Anonymous ,
when,httpsessionId,module,page,userId,actionType
2/13/2019 10:01:00,sid0,succession,9box,cgrant1,scm.mr.generate_howvswhat_report
2/13/2019 10:01:01,sid1,succession,talentsearch,cgrant1,scm.ts.list_saved_search
2/13/2019 10:01:02,sid0,succession,9box,cgrant1,scm.mr.export_howvswhat_report
2/13/2019 10:11:03,sid3,calibration,ManageCalibrationTemplates,hr1,cal.mct.create
2/13/2019 10:01:04,sid1,succession,talentsearch,cgrant1,scm.ts.start_over
2/13/2019 10:01:05,sid0,succession,9box,cgrant1,scm.mr.reset_filter
2/13/2019 10:01:06,sid1,succession,talentsearch,cgrant1,scm.ts.delete_saved_search
2/13/2019 10:11:07,sid3,calibration,ManageCalibrationTemplates,hr1,cal.mct.edit
2/13/2019 10:02:08,sid2,succession,talentsearch,lokamoto1,scm.ts.list_saved_search
2/13/2019 10:02:09,sid2,succession,talentsearch,lokamoto1,scm.ts.search
2/13/2019 10:01:20,sid0,succession,9box,cgrant1,scm.mr.export_howvswhat_report
2/13/2019 10:02:21,sid2,succession,talentsearch,lokamoto1,scm.ts.nominate
2/13/2019 10:11:22,sid3,calibration,ManageCalibrationTemplates,hr1,cal.mct.delete
2/13/2019 10:11:23,sid4,succession,talentsearch,athompson1,scm.ts.list_saved_search
Is above the original format of the loaded data? If so, please refer to applied steps in below Power Query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZPLbsMgEEV/pcoaOQanD3vbdXfdRZFF8NRG5WExUyefX2gWbSVs2dmwQOjoDPfO8bgTe17tRcnrB142JW/KkqHu4vGlFCBq71h99lem+iAdcYbKFjYUPTgIkqAd/GXCyyCpDTD6QLsTy0B5gvK/UJIGHCHIoIZ/cMLCaKQW5QRde3uQh4pVpnBNWis8eURWCVkxJY0+x/ES80062cPr78072NHE0ZENgaenhVVUqADxLi962DQ9koy+foKQpz2uGjsAArUf2tAc52mTVQcGYtzLqaQvfL77C6HTufqIpnxJTDGravyntJ78pgJFbL0Ru1BGsW5tVpcx6gm+Uc95q122hDEXIe7O5RZ9nvqzMIdZR0mDtyN6txTN6Rs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"when,httpsessionId,module,page,userId,actionType" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"when,httpsessionId,module,page,userId,actionType", type text}}), #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"), #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type2", [PromoteAllScalars=true]), #"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"when", type datetime}, {"httpsessionId", type text}, {"module", type text}, {"page", type text}, {"userId", type text}, {"actionType", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"when"}), #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"userId", Order.Ascending}}), #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[actionType]), "actionType", "httpsessionId", List.Count) in #"Pivoted Column"
Besides, I have attached the sample .pbix file for your reference.
Best regards,
Yuliana Gu
Hi @Anonymous ,
when,httpsessionId,module,page,userId,actionType
2/13/2019 10:01:00,sid0,succession,9box,cgrant1,scm.mr.generate_howvswhat_report
2/13/2019 10:01:01,sid1,succession,talentsearch,cgrant1,scm.ts.list_saved_search
2/13/2019 10:01:02,sid0,succession,9box,cgrant1,scm.mr.export_howvswhat_report
2/13/2019 10:11:03,sid3,calibration,ManageCalibrationTemplates,hr1,cal.mct.create
2/13/2019 10:01:04,sid1,succession,talentsearch,cgrant1,scm.ts.start_over
2/13/2019 10:01:05,sid0,succession,9box,cgrant1,scm.mr.reset_filter
2/13/2019 10:01:06,sid1,succession,talentsearch,cgrant1,scm.ts.delete_saved_search
2/13/2019 10:11:07,sid3,calibration,ManageCalibrationTemplates,hr1,cal.mct.edit
2/13/2019 10:02:08,sid2,succession,talentsearch,lokamoto1,scm.ts.list_saved_search
2/13/2019 10:02:09,sid2,succession,talentsearch,lokamoto1,scm.ts.search
2/13/2019 10:01:20,sid0,succession,9box,cgrant1,scm.mr.export_howvswhat_report
2/13/2019 10:02:21,sid2,succession,talentsearch,lokamoto1,scm.ts.nominate
2/13/2019 10:11:22,sid3,calibration,ManageCalibrationTemplates,hr1,cal.mct.delete
2/13/2019 10:11:23,sid4,succession,talentsearch,athompson1,scm.ts.list_saved_search
Is above the original format of the loaded data? If so, please refer to applied steps in below Power Query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZPLbsMgEEV/pcoaOQanD3vbdXfdRZFF8NRG5WExUyefX2gWbSVs2dmwQOjoDPfO8bgTe17tRcnrB142JW/KkqHu4vGlFCBq71h99lem+iAdcYbKFjYUPTgIkqAd/GXCyyCpDTD6QLsTy0B5gvK/UJIGHCHIoIZ/cMLCaKQW5QRde3uQh4pVpnBNWis8eURWCVkxJY0+x/ES80062cPr78072NHE0ZENgaenhVVUqADxLi962DQ9koy+foKQpz2uGjsAArUf2tAc52mTVQcGYtzLqaQvfL77C6HTufqIpnxJTDGravyntJ78pgJFbL0Ru1BGsW5tVpcx6gm+Uc95q122hDEXIe7O5RZ9nvqzMIdZR0mDtyN6txTN6Rs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"when,httpsessionId,module,page,userId,actionType" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"when,httpsessionId,module,page,userId,actionType", type text}}), #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"), #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type2", [PromoteAllScalars=true]), #"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"when", type datetime}, {"httpsessionId", type text}, {"module", type text}, {"page", type text}, {"userId", type text}, {"actionType", type text}}), #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"when"}), #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"userId", Order.Ascending}}), #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[actionType]), "actionType", "httpsessionId", List.Count) in #"Pivoted Column"
Besides, I have attached the sample .pbix file for your reference.
Best regards,
Yuliana Gu
Thank you.
Hey, @Anonymous
I have to admit that I'm not able to tell the difference between your question and the sample data you provided, for this reason start reading this post: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Some aspects seem to be quite detailed and some details will not be needed for each question, but as each question posted in this forum asks for time from other members, be polite and spent some time in advance to reduce the amount of time others have to spent answering your question.
Just open the action menu using the ellipsis button in the top right corner of your post:
Regards,
Tom
ok. thanks. now it's readable. can someone help on my difficulties?
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |