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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

[Need help ]transform user behavior data into a format that Sankey Diagram supported by PowerQuery

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

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-yulgu-msft
Employee
Employee

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thank you. 

TomMartens
Super User
Super User

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.



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

I notice the issue in my post. i am trying to find the edit button. but where is it???

Just open the action menu using the ellipsis button in the top right corner of your post:

 

image.png

 

Regards,

Tom



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

ok. thanks. now it's readable.  can someone help on my difficulties?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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