group column and visualization

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

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.