Unique values dynamically linked to multiple value columns

[user180618]

I am trying to transform data from a SharePoint list in Power Query Editor. That List has several single value columns and several multiple choice value columns. What I would like is to have those multiple values relate to one unique value (which will be used as the Count value in the Power BI report). Here's an example of what I have and what I've tried:

Name	Age	Locations lived	Languages spoken
John	30	[List]	[List]
Ben	35	[List]	[List]
Jerry	40	[List]	[List]

I've then applied 'Expand to new rows' to Locations lived and Languages spoken in Query Editor and got:

Name	Age	Locations lived	Languages spoken
John	30	New York	English
John	30	Chicago	Italian
Ben	35	New York	English
Ben	35	Seattle	French
Ben	35	Portland	null
Jerry	40	San Francisco	English
Jerry	40	Los Angeles	Spanish

But this duplicates my Name column, so when I create a Report to see a chart of the unique count of Names and the multiple Languages spoken, it will count John's as two, Ben's as two and so on. Instead, I would like the values New York and Chicago, and English and Italian, to relate to one count for John. 

If I do this another way and Extract values and concatenate, and then drag the 'Name' column to Values (under Visualizations), it will count John once but also show 'New York,Chicago' as one value and 'English,Italian' as one value.

Any suggestions as to how I can have multiple values in a column relate to just one unique column (in this case 'Name')?

I've tried choosing 'Count (Distinct)' for every chart created in the report, but this works for simple Visualizations, and skews the data for more complex ones. So I'd rather do it right and link column dynamically in Query Editor, rather than manually selecting Distinct Count in the report every time.

[v-juanli-msft]

Hi 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case and help the other members find it more quickly?

If not, please feel free to let me know.

Best Regards

Maggie

[user180618]

I've gone with extracting and concatenating the values, so now have:

Name	Age	Location lived	Languages spoken
John	30	New York,Chicago	English,Italian
Ben	35	New York,Seattle,Portland	English,French
Jerry	40	San Francisco,Los Angeles	English,Spanish

However, when I create a report and under Legend choose the column 'Location lived' and choose 'Count of Name' under Value, I cannot get it to show me the Location lived and Languages spoken values separately. So for example, I'd like a pie chart visualization for Ben to show me 1 count each for New York, Seattle and Portland, and 1 count each for English and French.

Is there a way to do this without running an R script in Query Editor?

[camargos88]

@user180618 ,

Why don't you split the values by comma, so you can achieve it.

Did I answer your question? Mark my post as a solution!
Ricardo

[user180618]

I've already split them by commas in Query Editor (see previous post), but it comes out like this in the report:

I'd like it to show me the count value for those cities separately, but for the Name column to be the main aggregator.

[camargos88 · Accepted solution]

Hi @user180618 

Check this file:  Download PBIX 

Did I answer your question? Mark my post as a solution!
Ricardo

[user180618]

Thanks @camargos88. I've done as you suggested: Extracted and concatenated, and then Split Column by delimeter. But I now get several separate Locationslived.1 and Locationslived.2 columns.

My next question is: How should I group the columns so that the report can get all of those values together (instead of adding every split column to the Legend)? If in Query Editor I select Locationslived.1, Locationslived.2 and so on and group by Sum, I get an Error. If I group by All rows, I just get a [Table].

(Again, bearing in mind these Locations columns are multiple choice values relating to one Name column value)

[camargos88]

Hi @user180618 ,

You have to split it on rows and not on columns, if you split it using columns you can't use both to aggregate as you want.

Take a look at the file again, it has a step splitting both columns on rows instead of columns.

Ricardo