Forum Discussion
Transform data: To Pivot or Unpivot, that is the question.
Hello! I am still fairly new to Power Query and am struggling with how to transform some data from excel files. I am trying to get from this:
| Service | Receive Date | Questions | Value | n | % |
| Hospice Services | 7/1/2020 - 7/31/2020 | *Recommend the hospice care | Definitely no | 0 | 0 |
| Hospice Services | 7/1/2020 - 7/31/2020 | *Recommend the hospice care | Probably no | 1 | 4 |
| Hospice Services | 7/1/2020 - 7/31/2020 | *Recommend the hospice care | Probably yes | 2 | 8 |
| Hospice Services | 7/1/2020 - 7/31/2020 | *Recommend the hospice care | Definitely yes | 22 | 88 |
| Hospice Services | 8/1/2020 - 8/31/2020 | *Recommend the hospice care | Definitely no | 0 | 0 |
| Hospice Services | 8/1/2020 - 8/31/2020 | *Recommend the hospice care | Probably no | 1 | 5 |
| Hospice Services | 8/1/2020 - 8/31/2020 | *Recommend the hospice care | Probably yes | 1 | 5 |
| Hospice Services | 8/1/2020 - 8/31/2020 | *Recommend the hospice care | Definitely yes | 18 | 90 |
| Hospice Services | 9/1/2020 - 9/30/2020 | *Recommend the hospice care | Definitely no | 1 | 4.35 |
| Hospice Services | 9/1/2020 - 9/30/2020 | *Recommend the hospice care | Probably no | 1 | 4.35 |
| Hospice Services | 9/1/2020 - 9/30/2020 | *Recommend the hospice care | Probably yes | 0 | 0 |
| Hospice Services | 9/1/2020 - 9/30/2020 | *Recommend the hospice care | Definitely yes | 21 | 91.3 |
Etc.
To this:
| Service | Receive Date | Question | Def_No_N | Prob_No_N | Prob_Yes_N | Def_Yes_N | Total_N |
| Hospice Services | 7/1/2020 - 7/31/2020 | *Recommend the hospice care | 0 | 1 | 2 | 22 | 25 |
| Hospice Services | 8/1/2020 - 8/31/2020 | *Recommend the hospice care | 0 | 1 | 1 | 18 | 20 |
| Hospice Services | 9/1/2020 - 9/30/2020 | *Recommend the hospice care | 1 | 1 | 0 | 21 | 23 |
There are also other questions in separate excel files that I need to transform in a similar fashion and ultimately merge together.
Thanks!
It is typically better to have data of the same type (in your case, questions and answers) left unpivoted to simplify analysis and visualization. If unpivoted, two tables would be appended vs merged.
I used your data and made no data transformation, and made a Matrix visual on the report page to get your desired results. See pic to see which field went where.
Pat
3 Replies
It is typically better to have data of the same type (in your case, questions and answers) left unpivoted to simplify analysis and visualization. If unpivoted, two tables would be appended vs merged.
I used your data and made no data transformation, and made a Matrix visual on the report page to get your desired results. See pic to see which field went where.
Pat
You should leave your data unpivoted, and append the data from the other files. You can pivot it back out in your visuals and write measures to get your desired results. "Rows before co-s" (columns).
Pat
I don't mean to sound glib, rather naive I suppose, but... Why would I do that? What is the value of "rows before co-s"?
Thanks!
