How to count answers and show variation of percentage points of two years dynamically?

[AliceFGX]

Hello,

I'm blocked whith this request, can anyone please show me the way? 🙂 

I have the answers of a poll like this (but the data is much larger of course) :

Person /year/country/ question A/ question B/ question C/

Alice/2021/BR/ Satisfied/Satisfied/Satisfied/

Pierre/2021/DE/Satisfied/Satisfied/Satisfied/

Adrian/2021/FRI don't know/I don't know/I don't know

Alice/2022/BR/not satisfied/Satisfied/Not Satisfied/

Pierre/2022/DE/not Satisfied/Not Satisfied/Satisfied/

Adrian/2022/FR/Satisfied/Satisfied/Satisfied/

I want to show a pivot table that summarizes data showing, for each question the % number of people of the total that answered satisfied in 2021, in 2022 and the number of the percentage points diference between the two years, for each country. 

for example in France between 2021 and 2022 there were x percent more people declaring being satisfied in question A.

I could get a table like this:

question #/BR 2021 % satisfied /BR 2022 % satisfied /evolution of %satisfaction 2021 vs 2022/ DE 2021 % satisfied /DE 2022 % satisfied /DE evolution of %satisfaction 2021 vs 2022/ FR 2021 % satisfied /FR 2022 % satisfied / FRevolution of %satisfaction 2021 vs 2022/ 

Ideally I would be able to do things in power query and later apply the data on analyse in excel and create a pivot table with the data and slicers instead of publishing in power BI and using DAX. Is it possible?

Thanks in advance!

[jbwtp · Accepted solution]

Hi @AliceFGX,

Potentially, something like this (if I understand the problem correctly):

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcszJTE5V0lEyMjAyBFJOQUBCITixJLM4LTM1BcjBzo7ViVYKyEwtKkLS6+KKUzm6VseUoszEPIRWN5C1ngop+XkxpQYGRuYlCtl5+eVEi4GNRHjECOaRvPwShWIcDvIDyuH2jxHMP3koytC14fOXEcxfRARJLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Person " = _t, year = _t, country = _t, #" question A" = _t, #" question B" = _t, #" question C" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Person ", type text}, {"year", Int64.Type}, {"country", type text}, {" question A", type text}, {" question B", type text}, {" question C", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Person ", "year", "country"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"country", "Attribute"}, {{"Count", each Table.Group(_, {"year"}, {{"%", each Table.RowCount(Table.SelectRows(_, each Text.Lower([Value]) = "satisfied")) / Table.RowCount(_), Percentage.Type}})}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"year", "%"}, {"year", "%"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"year", Int64.Type}, {"%", Percentage.Type}})
in
    #"Changed Type1"

You can then pivot years and calculate yoy difference, but I would suggest doing this in DAX.

Cheers,

John

[KT_Bsmart2gethe]

Hi @AliceFGX ,

Is this what you have in mind?

I would still prefer DAX in terms of performance.

Transformed Data format:

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Promoted Headers", {" Question A", " Question B", " Question C"}, "Question#", "Answer"),
    #"Trimmed Text" = Table.TransformColumns(#"Unpivoted Only Selected Columns",{{"Question#", Text.Trim, type text}, {"Answer", Text.Trim, type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Trimmed Text", {"Person ", "Year"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Year", "Attribute", "Value"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

File:

Sample File 

Regards

KT