Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi there,
I'm new in using Power BI, and am struggling in creating an automated report using data feed from an ongoing survey (MS Form). Going through the posts in this forum, I've learnt about unpivot column which is an excellent tip.
However, given the nature of the survey, one part of the Form consists of multiple sections (with 4 questions repeated 5 times as below):
a) Site type (drop down box for a single select answer)
b) Support provided (drop down box for a single select answer)
c) Time spent (number value)
d) Add another entry (which cycle back to a new section duplicated with questions a-d)
The answers collected in the Excel spreadsheet look like this:
Site type | Support provided | Time spent (GxP1) | Add new GxP "enhanced" support? | Site type (2) | Support provided (2) | Time spent (GxP2) | Add new GxP "enhanced" support?1 | Site type (3) | Support provided (3) | Time spent (GxP3) | Add new GxP "enhanced" support?2 | Site type (4) | Support provided (4) |
Offshore | New build | 5 | Yes | Office | PQS | 1.5 | Yes | Hospital | Facility & equipment | 3 | Yes | Onsite | Pathfinder site |
Onsite | Pathfinder site | 1 | Yes | Hospital | New build | 1 | Yes | Offshore | PQS | 1 | Yes | Hospital | Facility & equipment |
How can I present the data to show how much time is spent on each site type, and for each support provided? The problem with 'unpivot column' is that it gives me answers combining number and text under a single 'value' column.
Any advice is much appreciated. Thank you.
SMW
Solved! Go to Solution.
The column naming is inconsistent across groups. This needs to be corrected before re-pivoting
Code can be optimized
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZAxb4MwEIX/yslTIkVVgLRr1SXJVKjoUgEDxYc4CYyLTdP8+56TNILKkTJYtt+7++7ZWSZSsgj2qFGsRDpq3Q8W9NB/k0TJ0jt1CEajsrDY/STBkrUXKUHhAfgOuUDVlKpCmQsw5/5nh/rDwiJcetAX+R8+vAsfzPmRnx95+NFd/HDO3/j5LBerTEBc16bpB/d/8MrYz5Fa93OPvD7QONnVUHWqgOQt5T14mNj73miyZevO27KiluwR8nG9Dp8Av0bSHednM7q2xMpwPleflLapSUkc4CSdI920ebJ/7iz6pGb6ukv0qznpvx27KH4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Line", 1, 1, Int64.Type),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Line"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Site type","Site type (1)",Replacer.ReplaceValue,{"Attribute", "Value"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Support provided","Support provided (1)",Replacer.ReplaceValue,{"Attribute"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Add new GxP ""enhanced"" support?","Add new GxP ""enhanced"" support?(1)",Replacer.ReplaceValue,{"Attribute"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Add new GxP ""enhanced"" support?1","Add new GxP ""enhanced"" support?(2)",Replacer.ReplaceValue,{"Attribute"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Add new GxP ""enhanced"" support?2","Add new GxP ""enhanced"" support?(3)",Replacer.ReplaceValue,{"Attribute"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value4", "Attribute", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Attribute", "Index"}),
#"Replaced Value5" = Table.ReplaceValue(#"Split Column by Delimiter",")","",Replacer.ReplaceText,{"Index"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","GxP","",Replacer.ReplaceText,{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value6", List.Distinct(#"Replaced Value6"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
You can pivot that table if you drop the AnswerKey column.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJtT8IwEMe/SsNrNbC2a33pQ1RiohiMvpi8GFCkcWy1DwJJP7zHlhGQm0mz5O7+t9/9r82y3vA2xivvrZ4Gry4GMd5vRmRYztUmxre8CArKpVsrSx7Vtjc5y3pJjGPtFfFbA0XoeF4s3LKyu4DvFcGYynpibPWj52peC+/ymS6035KP0O8nKVHfQZuVKj1U07bzVa8UcabJxsjgK+oaO+GWbhefExhAzyDHkr0Qw18rZ6ov+IHNdanLT2igbcMfKoUaQ6gw30PljPZ5AQrejQPh6GUMmhQjJPVhja+0e5+C7hWYoSe1JtOgC4gEa5WnTgRHOMnB/m7ylQkOhGk3rjUkBAZqDAmJgOiRoctuAsVuSPYxHK2PHCA4duDrPbdqWQUHaZl0g9m/L1NSbALWTMCQCfjhG5G8m8uPblCmGIfXKAlLn/wC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Attribute.1", type text}, {"GxP Index", Int64.Type}, {"Value", type text}, {"Answer Key", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"ID", "Attribute.1", "GxP Index", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Attribute.1]), "Attribute.1", "Value")
in
#"Pivoted Column"
Following on my query above, I'm now struggling to create a pivot chart to show the amount of time spent for each 'site type' and for each type of 'support provided'.
Here's the table that I have produced so far, where the 'Answer Key' is connected to my main 'Responses' table.
Pivoting the table below failed to give me the chart that I need. Any help or advice would be much appreciated. Thank you so much!
best wishes,
SMW
ID | Attribute.1 | GxP Index | Value | Answer Key |
2 | Site type | 1 | Offshore | 15 |
2 | Support provided | 1 | Facility & equipment | 16 |
2 | Time spent | 1 | 4 | 17 |
4 | Site type | 1 | Onsite - Office | 42 |
4 | Support provided | 1 | Bespoke training | 43 |
4 | Time spent | 1 | 3 | 44 |
4 | Site type | 2 | Hospital | 45 |
4 | Support provided | 2 | PQS | 46 |
4 | Time spent | 2 | 2 | 47 |
6 | Site type | 1 | Offshore | 73 |
6 | Support provided | 1 | New build | 74 |
6 | Time spent | 1 | 3 | 75 |
6 | Site type | 2 | Onsite - Campus | 76 |
6 | Support provided | 2 | PQS | 77 |
6 | Time spent | 2 | 2 | 78 |
6 | Site type | 3 | Offshore | 79 |
6 | Support provided | 3 | Bespoke training | 80 |
6 | Time spent | 3 | 3 | 81 |
6 | Site type | 4 | Onsite - Warehouse | 82 |
6 | Support provided | 4 | Facility & equipment | 83 |
6 | Time spent | 4 | 3 | 84 |
6 | Site type | 5 | Hospital | 85 |
6 | Support provided | 5 | New build | 86 |
6 | Time spent | 5 | 4 | 87 |
What would be the expected outcome?
The expected outcome is this where each bar represent one site type. To do it, I had to use many to many relationship by joining up 'ID' of the responses; and remove 'Answer Key', which is not ideal. When I tried incorporating 'Answer key', pivoting the columns returned errors in some cells. Do you think that this is the compromise or is there a better way? Thanks.
best wishes,
SMW
You can pivot that table if you drop the AnswerKey column.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZJtT8IwEMe/SsNrNbC2a33pQ1RiohiMvpi8GFCkcWy1DwJJP7zHlhGQm0mz5O7+t9/9r82y3vA2xivvrZ4Gry4GMd5vRmRYztUmxre8CArKpVsrSx7Vtjc5y3pJjGPtFfFbA0XoeF4s3LKyu4DvFcGYynpibPWj52peC+/ymS6035KP0O8nKVHfQZuVKj1U07bzVa8UcabJxsjgK+oaO+GWbhefExhAzyDHkr0Qw18rZ6ov+IHNdanLT2igbcMfKoUaQ6gw30PljPZ5AQrejQPh6GUMmhQjJPVhja+0e5+C7hWYoSe1JtOgC4gEa5WnTgRHOMnB/m7ylQkOhGk3rjUkBAZqDAmJgOiRoctuAsVuSPYxHK2PHCA4duDrPbdqWQUHaZl0g9m/L1NSbALWTMCQCfjhG5G8m8uPblCmGIfXKAlLn/wC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Delimiter", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Attribute.1", type text}, {"GxP Index", Int64.Type}, {"Value", type text}, {"Answer Key", Int64.Type}}),
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"ID", "Attribute.1", "GxP Index", "Value"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns", List.Distinct(#"Removed Other Columns"[Attribute.1]), "Attribute.1", "Value")
in
#"Pivoted Column"
The column naming is inconsistent across groups. This needs to be corrected before re-pivoting
Code can be optimized
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZAxb4MwEIX/yslTIkVVgLRr1SXJVKjoUgEDxYc4CYyLTdP8+56TNILKkTJYtt+7++7ZWSZSsgj2qFGsRDpq3Q8W9NB/k0TJ0jt1CEajsrDY/STBkrUXKUHhAfgOuUDVlKpCmQsw5/5nh/rDwiJcetAX+R8+vAsfzPmRnx95+NFd/HDO3/j5LBerTEBc16bpB/d/8MrYz5Fa93OPvD7QONnVUHWqgOQt5T14mNj73miyZevO27KiluwR8nG9Dp8Av0bSHednM7q2xMpwPleflLapSUkc4CSdI920ebJ/7iz6pGb6ukv0qznpvx27KH4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Added Index" = Table.AddIndexColumn(#"Promoted Headers", "Line", 1, 1, Int64.Type),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Line"}, "Attribute", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Unpivoted Columns","Site type","Site type (1)",Replacer.ReplaceValue,{"Attribute", "Value"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Support provided","Support provided (1)",Replacer.ReplaceValue,{"Attribute"}),
#"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Add new GxP ""enhanced"" support?","Add new GxP ""enhanced"" support?(1)",Replacer.ReplaceValue,{"Attribute"}),
#"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Add new GxP ""enhanced"" support?1","Add new GxP ""enhanced"" support?(2)",Replacer.ReplaceValue,{"Attribute"}),
#"Replaced Value4" = Table.ReplaceValue(#"Replaced Value3","Add new GxP ""enhanced"" support?2","Add new GxP ""enhanced"" support?(3)",Replacer.ReplaceValue,{"Attribute"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value4", "Attribute", Splitter.SplitTextByEachDelimiter({"("}, QuoteStyle.Csv, false), {"Attribute", "Index"}),
#"Replaced Value5" = Table.ReplaceValue(#"Split Column by Delimiter",")","",Replacer.ReplaceText,{"Index"}),
#"Replaced Value6" = Table.ReplaceValue(#"Replaced Value5","GxP","",Replacer.ReplaceText,{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Replaced Value6", List.Distinct(#"Replaced Value6"[Attribute]), "Attribute", "Value")
in
#"Pivoted Column"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
27 | |
12 | |
12 | |
11 | |
9 |
User | Count |
---|---|
53 | |
28 | |
17 | |
14 | |
13 |