Issues with multiple columns generating a chart

[r0b0tn1k]

I have a multiselect column which I split using the split function.

Data original:

Column 1:

Agriculture, Wash, Infrastructure, Health

Data after:

Column 1:

Agriculture

Column 2:

Wash

Column 3:

Infrastructure

Column 4:

Health

Now each value in the data is represented as a separate column.

I want to do a chart of all the distinct values, but i'm hitting a stump.

If i try to add all the columns in to the values area, from reporting the actual values, it starts changing the label to "Count of column x".

Here's a demo of this happening.

demo

Any suggestions on how to resolve this would be great.

[kcantor]

In the query editor make sure you set the data to "Do Not Aggregate".

You can also select the drop down arrow beside the field name and use Quick Calc to show value as no calculation or the one that best fits your needs.

[r0b0tn1k]

Sorry, cannot find Do not aggregate anywhere in Query editor?

The aggregated value cannot be reported on as there are too many mixes of combinations (it's a multi select field), so you can get fields like:

Agriculture, Wash

Agriculture, Wash, Peacebuilding

Agriculture, Peacebuilding, Microfinance

From a DB perspective, it treats those as 3 separate values, not as 4 separate values.

[v-sihou-msft · Accepted solution]

@r0b0tn1k

I assume your data is like below. I’m not sure if you want to know distinct count of all items or count appearance of each items.

No matter which one is your goal, you don’t need to split and pivot the values into separated column. Just populate values into a single column. Please refer to steps below:

1. In Query Editor, right click this column and select “Replace Values”. Replace ‘, ‘ with ‘,’ and then no blank will exist between items.
2. Click “Add Index Column From 1” in the tab of “Add Column”.
   
3. Right click column 1 and select “Split Column By Delimiter”.
4. Right click Index column and select “Unpivot Other Columns”. To distinct count all items, you just need to "DistinctCount(Table[Value])".
   
5. Drag Doughnut chart into your canvas and set Legend and Values as below:
   

[r0b0tn1k]

Ok, figured more stuff out.

It appears the error is in the Changed type part of the query:

let
    Source = Web.Page(Web.Contents("https://global.crs.org/teams/gkim/lists/project%20log/export%20for%20glorious%20report.aspx")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{ {"Project Type", type text}, {"Project Priority", type text}, {"Project Category", type text}, {"Project Name", type text}, {"Project Status", type text}, {"Division", type text}, {"Project Sponsor", type text}, {"GKIM Lead", type text}, {"Technology Partner", type text}, {"Project Space", type text}, {"System Status", type text}, {"GKIM Support Requested", type text}, {"Selected Technology", type text}, {"ICT4D - Extended GKIM Support", type text}, {"ICT4D - Vendor or Internal Implementation", type text}, {"Project Milestones", type text}, {"Target Implementation Date", type date}, {"End of Project Outcome", type text}, {"Donor(s)", type text}, {"Total Project Budget", Int64.Type}, {"Country", type text}, {"Project Completion Workflow", type text}, {"Amount Budgeted for ICT4D", type text}, {"Gateway URL", type text}, {"ICT4D Project Notification", type text}, {"eValuate forms being used", type text}, {"ICT4D SME", type text}, {"ID", Int64.Type}, {"Project Name2", type text}, {"Project Name3", type text}, {"Project Team", type text}, {"Region", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.Contains([Project Type], "ICT4D") and [Project Category] = "ICT4D Systems" and Text.Contains([Project Status], "Open") and [GKIM Support Requested] = "Yes"),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Program Areas] <> null and [Program Areas] <> ""),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows1", "Index", 0, 1),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Index","Program Areas",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),{"Program Areas.1", "Program Areas.2", "Program Areas.3", "Program Areas.4", "Program Areas.5", "Program Areas.6", "Program Areas.7"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Program Areas.1", type text}, {"Program Areas.2", type text}, {"Program Areas.3", type text}, {"Program Areas.4", type text}, {"Program Areas.5", type text}, {"Program Areas.6", type text}, {"Program Areas.7", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type1", {"Index"}, "Attribute", "Value"),
    #"Filtered Rows2" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] = "Program Areas.1" or [Attribute] = "Program Areas.2" or [Attribute] = "Program Areas.3" or [Attribute] = "Program Areas.4" or [Attribute] = "Program Areas.5" or [Attribute] = "Program Areas.6" or [Attribute] = "Program Areas.7")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows2",{{"Value", "PR_Area_Value"}, {"Index", "the_Index"}})
in
    #"Renamed Columns"

The column is clearly there, and the error appears when I try to save the query. If I "allegedly" save this, then I dont see the new columns in the final result.

Here's the error, with the column reported as the error clearly visible.

[r0b0tn1k]

Ok, I figured it out. 

I filter attribute by Program Area 1, which seems to work.

However when i try to save the query, I get an error stating "Expression error: The column Project Type of the table wasnt found.

That's a column i'm doing filtering on?

[r0b0tn1k]

Just tried the same steps on another column that needs the same treatment, same thing happens...

[r0b0tn1k]

Thank you Simon, looks like it will do the job.

However, when I follow your steps, it creates attribute and values columns that have values made up from different columns than the ones I selected:

What might I be doing wong?

[r0b0tn1k]

I removed the change type that gets added automatically. It removed the issue.