Forum Discussion
Convert values of a column to a separate column
Hi,
Note: I am new to Power BI
I have a Power BI report built from a SharePoint list. This is a SharePoint form in which there are multiple fields that contain multi selectable items, but the values are fixed. For example, the following are the options -
| What do you want for dinner? |
| Pizza |
| Salad |
| Noodles |
When I extract these values in the query editor I get two options - Expand to new rows/Extract Values
I tried both of these when I chose to extract values, it concatenates the values chosen since my form lets people choose multiple options this isn't desirable as it is creating values for any and all combinations that are chosen. For example, if someone chooses Pizza and Noodles and someone else chooses Noodles and Salad it is creating values like - "Pizza; Noodles" then again "Noodles; Salad".
When I tried Expand to new rows it is creating multiple rows for that same person for all options chose by that person, again this isn't desirable. I don't want to create multiple rows for the same person.
I want to analyze at the end how many people chose each option like the example below -
| Name | Pizza | Salad | Noodles |
| w | 1 | ||
| x | 1 | 1 | |
| y | 1 | 1 | |
| z | 1 |
Is there a way I can convert my values(i.e Pizza, Salad, Noodles) to columns? I also have some people who haven't chosen any options. I have replaced the "null" values as "unspecified"
Any help would be appreciated.
Thanks but this worked for me - https://marque360.com/reporting-using-multi-value-fields-in-power-bi/
7 Replies
Please forgive me if you know what I'm saying here.
There is a difference between the format of the data and the format of the report that you want to see i.e. the visualisation.
The key thing to learn is to get the data model "right" so that creating the reports you want is more straightforward.
Am i right in thinking that after using 'Expand to New Rows' you get data that looks similar to this ?:
Person Food other column other column
w Noodles xx p
x Pizza yy q
x Noodles yy q
and so on
It sounds like you don't like it because data is repeated on each row but actually Powerbi likes columnar data.
In order to get a visual that you want from this, remove any columns you don't want and press 'Close and Apply'.
You could use a matrix to get the report you want with Person on the Rows, Food on the Columns and COUNT(Food) in Values.
Hope that helps. If not , post data and some pictures.
Thanks for your response!
When I tried to extract to new rows this is what happens -
Name What do you want for dinner w Noodles x Pizza x Noodles y Salad y Noodles z Pizza The reason I want them as separate columns is because I am counting the number of responses to this form as well, so when I am extracting them to different rows the count of responses increase. For the example provided above, I don't want to count person X's response as 2, which is what's happening right now after I extracted to new rows.
I did try the matrix visual and it seemed to work but it is messing up my response count overall. And when I concatenate I cant use this field as a slicer. I also cant remove columns, everything contains information I need. I hope I was able to explain my requirement.
