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
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!
Solved! Go to 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
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
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:
Regards
KT
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
68 | |
25 | |
18 | |
12 |