Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

group column and visualization

Hello community,

I am quite new to PowerBi and have faced the following issue:
there are 4 columns with data entries which may overlap (as shown below).

 

 

 

 

I need to create a chart which presents as a graph the sumarized information (for example how many people has choosen lidl as first option/second etc, how many have choosen Billa as first/second etc):


How can I do that, as when I do the above chart it is taking the data only from the first column, ignoring the rest.

Thanks in advance

Martin

 

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    Anonymous 

    For this you need to transform your data into two columns: Paste below M Code into blank query > Advanced editor in Power Query and check the steps. Make sure to change the file location

    let
    Source = Excel.Workbook(File.Contents("C:\File.xlsx"), null, true),
    #"page-1_table-1_Sheet" = Source{[Item="page-1_table-1",Kind="Sheet"]}[Data],
    #"Transposed Table" = Table.Transpose(#"page-1_table-1_Sheet"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Options"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Value] <> ""))
in
    #"Filtered Rows"

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

     

     

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Create a separate table by entering data.

     

    Then create these measures.

    Option1 Count = 
SWITCH (
    MAX ( 'Table (2)'[Name] ),
    "Lidl", CALCULATE ( COUNT ( 'Table'[Option1] ), 'Table'[Option1] = "Lidl" ),
    "AHTACTNKO", CALCULATE ( COUNT ( 'Table'[Option1] ), 'Table'[Option1] = "AHTACTNKO" ),
    "Kaufland", CALCULATE ( COUNT ( 'Table'[Option1] ), 'Table'[Option1] = "Kaufland" ),
    "Billa", CALCULATE ( COUNT ( 'Table'[Option1] ), 'Table'[Option1] = "Billa" )
)
    Option2 Count = 
SWITCH (
    MAX ( 'Table (2)'[Name] ),
    "Lidl", CALCULATE ( COUNT ( 'Table'[Option2] ), 'Table'[Option2] = "Lidl" ),
    "AHTACTNKO", CALCULATE ( COUNT ( 'Table'[Option2] ), 'Table'[Option2] = "AHTACTNKO" ),
    "Kaufland", CALCULATE ( COUNT ( 'Table'[Option2] ), 'Table'[Option2] = "Kaufland" ),
    "Billa", CALCULATE ( COUNT ( 'Table'[Option2] ), 'Table'[Option2] = "Billa" )
)
    Option3 Count = 
SWITCH (
    MAX ( 'Table (2)'[Name] ),
    "Lidl", CALCULATE ( COUNT ( 'Table'[Option3] ), 'Table'[Option3] = "Lidl" ),
    "AHTACTNKO", CALCULATE ( COUNT ( 'Table'[Option3] ), 'Table'[Option3] = "AHTACTNKO" ),
    "Kaufland", CALCULATE ( COUNT ( 'Table'[Option3] ), 'Table'[Option3] = "Kaufland" ),
    "Billa", CALCULATE ( COUNT ( 'Table'[Option3] ), 'Table'[Option3] = "Billa" )
)
    Option4 Count = 
SWITCH (
    MAX ( 'Table (2)'[Name] ),
    "Lidl", CALCULATE ( COUNT ( 'Table'[Option4] ), 'Table'[Option4] = "Lidl" ),
    "AHTACTNKO", CALCULATE ( COUNT ( 'Table'[Option4] ), 'Table'[Option4] = "AHTACTNKO" ),
    "Kaufland", CALCULATE ( COUNT ( 'Table'[Option4] ), 'Table'[Option4] = "Kaufland" ),
    "Billa", CALCULATE ( COUNT ( 'Table'[Option4] ), 'Table'[Option4] = "Billa" )
)

     

    The result is this.

     

     

    You can check more details from here.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    Anonymous 

    For this you need to transform your data into two columns: Paste below M Code into blank query > Advanced editor in Power Query and check the steps. Make sure to change the file location

    let
    Source = Excel.Workbook(File.Contents("C:\File.xlsx"), null, true),
    #"page-1_table-1_Sheet" = Source{[Item="page-1_table-1",Kind="Sheet"]}[Data],
    #"Transposed Table" = Table.Transpose(#"page-1_table-1_Sheet"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Transposed Table", {"Column1"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1", "Options"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([Value] <> ""))
in
    #"Filtered Rows"

    ________________________

    If my answer was helpful, please consider Accept it as the solution to help the other members find it

    Click on the Thumbs-Up icon if you like this reply 🙂

    YouTube  LinkedIn

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      You can indeed unpivot the data; but in real context you need to take in account the filters upon the main data table. How can you do that in the unpivoted table ? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Create a separate table by entering data.

     

    Then create these measures.

    Option1 Count = 
SWITCH (
    MAX ( 'Table (2)'[Name] ),
    "Lidl", CALCULATE ( COUNT ( 'Table'[Option1] ), 'Table'[Option1] = "Lidl" ),
    "AHTACTNKO", CALCULATE ( COUNT ( 'Table'[Option1] ), 'Table'[Option1] = "AHTACTNKO" ),
    "Kaufland", CALCULATE ( COUNT ( 'Table'[Option1] ), 'Table'[Option1] = "Kaufland" ),
    "Billa", CALCULATE ( COUNT ( 'Table'[Option1] ), 'Table'[Option1] = "Billa" )
)
    Option2 Count = 
SWITCH (
    MAX ( 'Table (2)'[Name] ),
    "Lidl", CALCULATE ( COUNT ( 'Table'[Option2] ), 'Table'[Option2] = "Lidl" ),
    "AHTACTNKO", CALCULATE ( COUNT ( 'Table'[Option2] ), 'Table'[Option2] = "AHTACTNKO" ),
    "Kaufland", CALCULATE ( COUNT ( 'Table'[Option2] ), 'Table'[Option2] = "Kaufland" ),
    "Billa", CALCULATE ( COUNT ( 'Table'[Option2] ), 'Table'[Option2] = "Billa" )
)
    Option3 Count = 
SWITCH (
    MAX ( 'Table (2)'[Name] ),
    "Lidl", CALCULATE ( COUNT ( 'Table'[Option3] ), 'Table'[Option3] = "Lidl" ),
    "AHTACTNKO", CALCULATE ( COUNT ( 'Table'[Option3] ), 'Table'[Option3] = "AHTACTNKO" ),
    "Kaufland", CALCULATE ( COUNT ( 'Table'[Option3] ), 'Table'[Option3] = "Kaufland" ),
    "Billa", CALCULATE ( COUNT ( 'Table'[Option3] ), 'Table'[Option3] = "Billa" )
)
    Option4 Count = 
SWITCH (
    MAX ( 'Table (2)'[Name] ),
    "Lidl", CALCULATE ( COUNT ( 'Table'[Option4] ), 'Table'[Option4] = "Lidl" ),
    "AHTACTNKO", CALCULATE ( COUNT ( 'Table'[Option4] ), 'Table'[Option4] = "AHTACTNKO" ),
    "Kaufland", CALCULATE ( COUNT ( 'Table'[Option4] ), 'Table'[Option4] = "Kaufland" ),
    "Billa", CALCULATE ( COUNT ( 'Table'[Option4] ), 'Table'[Option4] = "Billa" )
)

     

    The result is this.

     

     

    You can check more details from here.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.