Forum Discussion

See_Mun's avatar
See_Mun
Regular Visitor
2 years ago
Solved

Automate report from MS Form survey

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 u...
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    2 years ago

    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.

  • lbendlin's avatar
    lbendlin
    2 years ago

    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"
See_Mun's avatar
See_Mun
Regular Visitor
2 years ago

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

lbendlin's avatar
lbendlin
Super User
2 years ago

What would be the expected outcome?

  • See_Mun's avatar
    See_Mun
    Regular Visitor
    2 years ago

    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

    • lbendlin's avatar
      lbendlin
      Super User
      2 years ago

      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"