Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Help Wanted

Jump to Best Answer
  • 1.  Help Wanted

    Posted Jul 12, 2019 12:54 PM
      |   view attached
    Hello everyone,

    I have a dataset, sample attached, which is the response to a food survey for a community outreach program. The response to each question ranges between 1 and 5 and wanted to arranged the output  such that the questions displayed rowise and the count of selected options are displayed column-wise as shown in the example below. I was able to complete this by create a summary table using CountIF in Excel but was hoping PowerBI could do the same.


    Any help is appreciated.

    ------------------------------
    Ibrahim Badaru
    ------------------------------

    Attachment(s)

    xlsx
    SurveyData.xlsx   12K 1 version
    Conference-PBI_200x200


  • 2.  RE: Help Wanted

    Top Contributor
    Posted Jul 12, 2019 01:37 PM
      |   view attached

    Hi @Ibrahim Badaru:

    Sure. I kept everything within the Excel Worksheet​ but you can do the same functionality in PowerBI. The following are the steps I took:

    (1) Unpivoted your data set naming one column [CATEGORY] and the other [RESPONSE]
    (2) I generated a pivot table (you can do this with a matrix in PBI, etc.) and added [CATEGORY] to the rows and [RESPONSE] to the columns
    (3) I added the 'count' or [RESPONSES] to the values.

    The data set you uploaded had different information than your example, but everything seems to work fine. 

    Hope this helps,
    William



    ------------------------------
    William Rodriguez
    Business Analyst | MCSA: BI Reporting
    ------------------------------

    Attachment(s)

    xlsx
    Ibrahim_Help.xlsx   100K 1 version
    Conference-PBI_200x200


  • 3.  RE: Help Wanted
    Best Answer

    Gold Contributor
    Posted Jul 14, 2019 02:22 PM
      |   view attached
    @Ibrahim Badaru,
    It is very simple to create in Power BI as @William Rodriguez has pointed out.  I have attached the PBI sample for you providing two ways to manage the data, one pivoted counts and one unpivot counts which takes your excel spreadsheets and preps it within the query editor.



    Sample One M Query
    let
        Source = Excel.Workbook(File.Contents("D:\Power BI User Group Helping Files\Survey Responses\Ibrahim Badaru Survey Data.xlsx"), null, true),
        Responsee_Sheet = Source{[Item="Responsee",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Responsee_Sheet, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"White_Rice", Int64.Type}, {"Beans_Dodo", Int64.Type}, {"Beans_Bread", Int64.Type}, {"Pizza", Int64.Type}, {"Spaghetti", Int64.Type}, {"Chicken_Alfredo", Int64.Type}, {"Mac_Cheese", Int64.Type}, {"Greens", Int64.Type}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17"}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value"),
        #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Survey Question"}, {"Value", "Response"}}),
        #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Survey Question", Order.Ascending}}),
        #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Survey Question", "Response"}, {{"Count", each Table.RowCount(_), type number}}),
        #"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"Response", Order.Ascending}})
    in
        #"Sorted Rows1"​

    Sample Two M Query
    let
        Source = Excel.Workbook(File.Contents("D:\Power BI User Group Helping Files\Survey Responses\Ibrahim Badaru Survey Data.xlsx"), null, true),
        Responsee_Sheet = Source{[Item="Responsee",Kind="Sheet"]}[Data],
        #"Promoted Headers" = Table.PromoteHeaders(Responsee_Sheet, [PromoteAllScalars=true]),
        #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"White_Rice", Int64.Type}, {"Beans_Dodo", Int64.Type}, {"Beans_Bread", Int64.Type}, {"Pizza", Int64.Type}, {"Spaghetti", Int64.Type}, {"Chicken_Alfredo", Int64.Type}, {"Mac_Cheese", Int64.Type}, {"Greens", Int64.Type}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}}),
        #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17"}),
        #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value"),
        #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Survey Question"}, {"Value", "Response"}}),
        #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Survey Question", Order.Ascending}}),
        #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Survey Question", "Response"}, {{"Count", each Table.RowCount(_), type number}}),
        #"Sorted Rows1" = Table.Sort(#"Grouped Rows",{{"Response", Order.Ascending}}),
        #"Changed Type1" = Table.TransformColumnTypes(#"Sorted Rows1",{{"Count", Int64.Type}}),
        #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type1", {{"Response", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type1", {{"Response", type text}}, "en-US")[Response]), "Response", "Count", List.Sum),
        #"Renamed Columns1" = Table.RenameColumns(#"Pivoted Column",{{"1", "Response 1"}, {"2", "Response 2"}, {"3", "Response 3"}, {"4", "Response 4"}, {"5", "Response 5"}})
    in
        #"Renamed Columns1"​
    ​​​

    ------------------------------
    Brian Connelly
    Sr. Associate, RSM US
    Northeast Business Intelligence Practice
    ------------------------------

    Attachment(s)

    Conference-PBI_200x200