Please help with transforming a table

[arb1782]

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 ID	Form	Date	Controlname	Controllabel	answervalue
1	Outcomes Scoring Questionnaire	23/01/2021	healthy	Healthy	Sexually health
2	Outcomes Scoring Questionnaire	26/01/2021	active	Active	Active with the family
1	Outcomes Scoring Questionnaire	29/01/2021	safehome	Safe home	Safety in the family
1	Outcomes Scoring Questionnaire	23/01/2021	sexuallyhealthyoutcome	Sexually healthy outcome	Compliance with medical treatment
2	Outcomes Scoring Questionnaire	26/01/2021	activeoutcome	Active outcome	Shared family experiences
1	Outcomes Scoring Questionnaire	29/01/2021	safehomeoutcome	Safe home outcome	Safe home/service environment
1	Outcomes Scoring Questionnaire	23/01/2021	sho_currentscore	Current score	6
2	Outcomes Scoring Questionnaire	26/01/2021	ao_currentscore	Current score	5
1	Outcomes Scoring Questionnaire	29/01/2021	sho_currentscore	Current score	4
1	Outcomes Scoring Questionnaire	23/01/2021	sho_previousscore	Previous score	3
2	Outcomes Scoring Questionnaire	26/01/2021	ao_previousscore	Previous score	2
1	Outcomes Scoring Questionnaire	29/01/2021	sho_previousscore	Previous score	1

Desired structure (roughly)

Person ID	Form	Date	Outcome type level 1	Outcome type level 2	Outcome description	Previous score	Current score
1	Outcomes Scoring Questionnaire	23/01/2021	Healthy	Sexually healthy outcome	Compliance with medical treatment	3	6
2	Outcomes Scoring Questionnaire	26/01/2021	Active	Active outcome	Shared family experiences	2	5
1	Outcomes Scoring Questionnaire	29/01/2021	Safe home	Safe home outcome	Safe home/service environment	1	5

Thanks

arb

[v-jingzhang · Accepted solution]

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:

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.

[lbendlin]

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]

"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).

[arb1782]

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.