cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arb1782
Helper III
Helper III

Please help with transforming a table

Hi

 

I'm stuck on transforming a table into a new structure. I've tried various things, mainly involving different combinations of filtering, pivoting and unpivoting.

 

Note that the 'controlname' is unchanging so is the field to filter on. 'Controllabel' is what the users should say but it can change so should not be filtered or sorted on.

 

Current structure (roughly)

Person IDFormDateControlnameControllabelanswervalue
1Outcomes Scoring Questionnaire23/01/2021healthyHealthySexually health
2Outcomes Scoring Questionnaire26/01/2021activeActiveActive with the family
1Outcomes Scoring Questionnaire29/01/2021safehomeSafe homeSafety in the family
1Outcomes Scoring Questionnaire23/01/2021sexuallyhealthyoutcomeSexually healthy outcomeCompliance with medical treatment
2Outcomes Scoring Questionnaire26/01/2021activeoutcomeActive outcomeShared family experiences
1Outcomes Scoring Questionnaire29/01/2021safehomeoutcomeSafe home outcomeSafe home/service environment
1Outcomes Scoring Questionnaire23/01/2021sho_currentscoreCurrent score6
2Outcomes Scoring Questionnaire26/01/2021ao_currentscoreCurrent score5
1Outcomes Scoring Questionnaire29/01/2021sho_currentscoreCurrent score4
1Outcomes Scoring Questionnaire23/01/2021sho_previousscorePrevious score3
2Outcomes Scoring Questionnaire26/01/2021ao_previousscorePrevious score2
1Outcomes Scoring Questionnaire29/01/2021sho_previousscorePrevious score1

 

Desired structure (roughly)

Person IDFormDateOutcome type level 1Outcome type level 2Outcome descriptionPrevious scoreCurrent score
1Outcomes Scoring Questionnaire23/01/2021HealthySexually healthy outcomeCompliance with medical treatment36
2Outcomes Scoring Questionnaire26/01/2021ActiveActive outcomeShared family experiences25
1Outcomes Scoring Questionnaire29/01/2021Safe homeSafe home outcomeSafe home/service environment1

 

 

5

 

Thanks

arb

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @arb1782 

 

Please create a blank query and copy below codes into its Advanced Editor to check the result. Note that I remain only a outcome type column in the result and use original data's answervalue for it. I think the result is close to what you need. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZLBbsIwDEB/xeoZCSgb0o4TaNpOY+KI0OQVs1hKYpSkhf79AhTItEnd2lNsJ322n7paZQtyXiy8zLNB9iTOxGOOgeIxExucaIsmyTR+kI4pWr8nV6EuKVsPVtk41l7LUIghD8tCHNtPeCvJBxZrkd2RkU+Go/EwH+XH14pQB1XH6PkaLelQotY1nC9P5PxP5GlCxiJwdaw+fg9gz0FBUARbNKzrfwz+kOA9bknJScoyhpDEoQa23TqkanyjoVEk549/+qnhdjUTs9OMtmjWNLThAjUERxgM2dBL5q1PozKZSaGjTbMw0GFHjimO4XvqTTpcLMMvtaGPvyHHrclW7MReV+2gXcl7UToXCT6+P1k9p3DJp10ttpLvu+pqH/quh4+do4ql9BfWosmv8El3I63svIeTVvg4W6+/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Person ID", Int64.Type}, {"Form", type text}, {"Date", type text}, {"Controlname", type text}, {"Controllabel", type text}, {"answervalue", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.EndsWith([Controlname], "outcome") then "outcome" else if Text.EndsWith([Controlname], "currentscore") then "currentscore" else if Text.EndsWith([Controlname], "previousscore") then "previousscore" else "type"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Custom]), "Custom", "answervalue"),
    #"Grouped Rows" = Table.Group(#"Pivoted Column", {"Person ID", "Form", "Date"}, {{"Outcome type", each List.Max([type]), type nullable text}, {"Outcome description", each List.Max([outcome]), type nullable text}, {"Previous score", each List.Max([previousscore]), type nullable text}, {"Current score", each List.Max([currentscore]), type nullable text}})
in
    #"Grouped Rows"

Result:

020301.jpg

 

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

View solution in original post

4 REPLIES 4
v-jingzhang
Community Support
Community Support

Hi @arb1782 

 

Please create a blank query and copy below codes into its Advanced Editor to check the result. Note that I remain only a outcome type column in the result and use original data's answervalue for it. I think the result is close to what you need. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZLBbsIwDEB/xeoZCSgb0o4TaNpOY+KI0OQVs1hKYpSkhf79AhTItEnd2lNsJ322n7paZQtyXiy8zLNB9iTOxGOOgeIxExucaIsmyTR+kI4pWr8nV6EuKVsPVtk41l7LUIghD8tCHNtPeCvJBxZrkd2RkU+Go/EwH+XH14pQB1XH6PkaLelQotY1nC9P5PxP5GlCxiJwdaw+fg9gz0FBUARbNKzrfwz+kOA9bknJScoyhpDEoQa23TqkanyjoVEk549/+qnhdjUTs9OMtmjWNLThAjUERxgM2dBL5q1PozKZSaGjTbMw0GFHjimO4XvqTTpcLMMvtaGPvyHHrclW7MReV+2gXcl7UToXCT6+P1k9p3DJp10ttpLvu+pqH/quh4+do4ql9BfWosmv8El3I63svIeTVvg4W6+/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t, #"(blank).4" = _t, #"(blank).5" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Person ID", Int64.Type}, {"Form", type text}, {"Date", type text}, {"Controlname", type text}, {"Controllabel", type text}, {"answervalue", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.EndsWith([Controlname], "outcome") then "outcome" else if Text.EndsWith([Controlname], "currentscore") then "currentscore" else if Text.EndsWith([Controlname], "previousscore") then "previousscore" else "type"),
    #"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[Custom]), "Custom", "answervalue"),
    #"Grouped Rows" = Table.Group(#"Pivoted Column", {"Person ID", "Form", "Date"}, {{"Outcome type", each List.Max([type]), type nullable text}, {"Outcome description", each List.Max([outcome]), type nullable text}, {"Previous score", each List.Max([previousscore]), type nullable text}, {"Current score", each List.Max([currentscore]), type nullable text}})
in
    #"Grouped Rows"

Result:

020301.jpg

 

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

View solution in original post

lbendlin
Super User III
Super User III

You can't do that. Unless you have another unique identifier that stays the same and has the same meaning. Meta data consistency is one of the fundamental requirements of reporting. Sadly it is often violated, as in your scenario.

lbendlin
Super User III
Super User III

"healthy" and "sexuallyhealthyoutcome"  don't obey your expected pattern.

 

[ControlLabel] is a critical part of your process as it is the only way to infer the current/previous score control names (like "sho_currentscore" - taken from the first letters of each word of the ControlLabel).

Hi @lbendlin 

 

I'm not sure I understand what you're getting it. You're right ControlLabel is an important part. My point was that over time the controllabel might change but its corresponding controlname is unchanging. So for example:

 

Next week, the controlabel 'safe home' may change to 'safety at home' but the corresponding controlname will remain as 'safehome'. I need to avoid having to change the steps in the process every time a controllabel changes.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.