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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors