Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
AliceFGX
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION
jbwtp
Memorable Member
Memorable Member

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

View solution in original post

2 REPLIES 2
jbwtp
Memorable Member
Memorable Member

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
Impactful Individual
Impactful Individual

Hi @AliceFGX ,

 

Is this what you have in mind?

I would still prefer DAX in terms of performance.

 

KT_Bsmart2gethe_0-1668459492801.png

 

Transformed Data format:

KT_Bsmart2gethe_1-1668459553877.png

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors