Pivot showing percentage of column total

[k1s1]

Hello,

I'm stil trying to get my head round when & how to pivot colmuns

If my data looks like the following:

Q1	Q2	Q3	Q4
Neutral	Like	Dislike	Like
Dislike	Like	Like	Dislike
Dislike	Like
Like		Like	Like
Dislike	Dislike	Like	Dislike
	Dislike
Like	Neutral	Like	Like
	Like	Dislike	Like

How should I pivot to get counts of the number of each type (excluding blanks), showing the percentage of the column total, so that the ouput looks like this?:

Column1	Q1	Q2	Q3	Q4
Like	33%	57%	67%	67%
Dislike	50%	28%	33%	33%
Neutral	17%	14%

The steps I used are first to replace the 'empty' cells with null, then add an index column, then slect the other columns and click Unpivot, to get something like below. 

Index	Attribute	Value
0	Q1	Neutral
0	Q2	Like
0	Q3	Dislike
0	Q4	Like
1	Q1	Dislike
1	Q2	Like
1	Q3	Like
1	Q4	Dislike
2	Q1	Dislike
2	Q2	Like
2	Q3
2	Q4
3	Q1	Like
3	Q2
3	Q3	Like
3	Q4	Like
4	Q1	Dislike
4	Q2	Dislike
4	Q3	Like
4	Q4	Dislike
5	Q1
5	Q2	Dislike
5	Q3
5	Q4
6	Q1	Like
6	Q2	Neutral
6	Q3	Like
6	Q4	Like
7	Q1
7	Q2	Like
7	Q3	Dislike
7	Q4	Like

Then I highlighted the Index column, and selected pivot from the GUI. Under advanced options I selected "Count (Not Blank)". ..which gives me the interim table:

Column1	Q1	Q2	Q3	Q4
Like	2	4	4	4
Dislike	3	2	2	2
Neutral	1	1

But how can I get these values to be percentages of the column total instead?

Here's my code so far:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstLSlKzFHSUfLJzE4FUi6ZxTkQFlggVicaXQhDKVY1IASSQHB9cBqLoRnZZBR5NFMxXO+DpAuHh2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Q1", "Q2", "Q3", "Q4"}),
    #"Add dummy column upon which to pivot" = Table.AddIndexColumn(#"Replaced Value", "Highlight me to Pivot", 0, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Add dummy column upon which to pivot", {"Highlight me to Pivot"}, "Attribute", "Value"),
    #"Pivoted on Highlight me to Pivot" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Highlight me to Pivot", List.NonNullCount)
in
    #"Pivoted on Highlight me to Pivot"

[k1s1 · Accepted solution]

I found out how to do the last step (percentage of column) total here:  https://exceloffthegrid.com/power-query-percent-of-total/

[k1s1 · Accepted solution]

In case it helps somebody else, here's my code:

let

// Next 2 lines creates the dummy data table - replace with your source 
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstLSlKzFHSUfLJzE4FUi6ZxTkQFlggVicaXQhDKVY1IASSQHB9cBqLoRnZZBR5NFMxXO+DpAuHh2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Q1 = _t, Q2 = _t, Q3 = _t, Q4 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"",null,Replacer.ReplaceValue,{"Q1", "Q2", "Q3", "Q4"}),

// This doesn't need to be named as such, but avoids confusion later (The 'Values Column' in the Pivot step is NOT the column named 'Values' in the prior Unpivoting step)
    #"Add dummy column upon which to pivot" = Table.AddIndexColumn(#"Replaced Value", "Select this column as Values Column in pivot step", 0, 1, Int64.Type),

//Then select all the columns except the one just added above, a click choose unpivot columns from the Transform menu
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Add dummy column upon which to pivot", {"Select this column as Values Column in pivot step"}, "Attribute", "Value"),
 
 // Next step isn't necessary - just makes it more obvious to avoid the miselecting the Values column
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Attribute highlight this column to pivot"}}),
 
// Then highlight /select the Attribute highlight this column to pivot, then Transform > Pivot from the menu.  elect the dummy / index column as the Values Column (named earlier: "Select this column as Values Column in pivot step") 
    #"Pivoted Attribute Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[#"Attribute highlight this column to pivot"]), "Attribute highlight this column to pivot", "Select this column as Values Column in pivot step", List.NonNullCount),

//Now to add a percentage of column total for each:
    #"Added Custom" = Table.AddColumn(#"Pivoted Attribute Column", "Q1 % of column", each [Q1]/List.Sum(#"Pivoted Attribute Column"[Q1])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Q2 % of column", each [Q2]/List.Sum(#"Added Custom"[Q2])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Q3 % of column", each [Q3]/List.Sum(#"Added Custom"[Q3])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Q4 % of column", each [Q4]/List.Sum(#"Added Custom2"[Q4])),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom3",{{"Q1 % of column", Percentage.Type}, {"Q2 % of column", Percentage.Type}, {"Q3 % of column", Percentage.Type}, {"Q4 % of column", Percentage.Type}})
in
    #"Changed Type"

(Paste this into a new /blank Query using the Advanced Editor to see how it works. 

[k1s1 · Accepted solution]

I found out how to do the last step (percentage of column) total here:  https://exceloffthegrid.com/power-query-percent-of-total/