Convert values of a column to a separate column

[prita]

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.

[Greg_Deckler]

Invoking @ImkeF 

---

---

[HotChilli]

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.

[prita]

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. 

[HotChilli]

The data looks good in this format.

Response count will be a distinct count of the Name column. Is that right?

What do you mean "And when I concatenate I cant use this field as a slicer."?

If you want to, draw a picture of the desired output.

[prita]

---

@HotChilli wrote:

The data looks good in this format.

Response count will be a distinct count of the Name column. Is that right?

What do you mean "And when I concatenate I cant use this field as a slicer."?

If you want to, draw a picture of the desired output.

---

My data is more complicated than that, there are people who have entered two different responses if x has chosen "Noodles & Pizza" in one instance and "Pizza & salad" in another response I want to treat them as different responses. I do have a unique ID for each response but when multiple rows are created for the same response the same ID for a unique response is counted multiple times. I might have to check the data type of the ID as well.

"And when I concatenate I cant use this field as a slicer." - By this I mean on concatenating the values are coming with a comma or semicolon and for each combination of options chosen it creates a different value.

If X chooses "Noodles & Pizza", Y chooses "Salad & Noodles" andW chooses "Noodles", I get values as follows - 

W	Noodles
X	Pizza; Noodles
Y	Salad; Noodles
Z	Pizza

I can use it as a slicer when the values come like this.

I found this post it is more on the lines of what I am trying to achieve https://marque360.com/reporting-using-multi-value-fields-in-power-bi/. Hopefully, this will work.

[ImkeF]

Having problems to understand the exact requirement here, but the expansion of the list to rows seems the way to go in my eyes.

This code shows how transform it further to the format you've requested in your original post: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKlfSUfLLz0/JSS1WitWJVqoA8gMyq6oS4Txk2UogPzgxJzEFzkOWrULojQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Name = _t, #"What do you want for dinner" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"What do you want for dinner", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Name", "Name - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"What do you want for dinner"]), "What do you want for dinner", "Name - Copy", List.Count)
in
    #"Pivoted Column"

[prita · Accepted solution]

Thanks but this worked for me -   https://marque360.com/reporting-using-multi-value-fields-in-power-bi/