Hierarchy horizontal expand on a table.

[LazarosC]

Hello everybody

I get the usuall expand option in the table with the +/- symbol. Whenever i press it all the components of the specific hierarchy
expand as they should.

What i want to do is, instead of a vertical expanding, to get an horizontal expanding with every row containing the city that belongs to the corresponding cluster. Below there's a screenshot of an excel file showing the final result.

Each row should contain only the cities that are in the specific cluster, in the same way that each column contains only the related cities.

Hopefully i provided every useful information.

Any help is highly appreciated.

Thank you for your time

Kind Regards

Lazaros

[lbendlin]

I get the usuall expand option in the table with the +/- symbol.

No, the table doesn't have that option.  You mean the matrix visual?

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[LazarosC]

Thank you very much for replying.

Sorry that i didnt explain it clearly, yes i meant the option from the matrix visual indeed. Where you can choose to expand it and you get the vertical expanding.

Bellow i will post some simplyfied data that show the issue i have.

Region	Product
A	1
B	2
C	3
D	5
A	4
B	2
C	3
D	5
E	2

The expected outcome should look like this. 

Each row needs to show only the products that fall into the corresponding region, 1 or more products.

On the picture i posted on the original thread, that was achieved verticaly in power bi with the expand option that is available in the matrix visual (+/- symbol). I try to find a way to achieve it with horizontaly view.

Both Region and products are in the same table.

Can this be achieved by measure or there's another way?

Hopefully i provided all the information needed to make it clear.

Thank you very much for your time.

Cheers

[lbendlin]

Please show the expected outcome based on the sample data you provided.

[LazarosC]

Hello, 

I will reprovide the sample data and then the desired outcome based on the exact sample data.

Region	Product
A	1
B	2
Α	4
C	3
D	5
D	6
E	12
F	8
H	10
F	9
H	11

And the expected outsome should look like this

Each region may contain more than one products, and each product is uniquely matched only in 1 region.

Hope this one helps

Thank you very much

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTJUitWJVnICsozALJCYCZjlDGQZg1kuQJYpnGUGZrmC9EK0uAGZFmCWB0jQAC5oiRAEWhMLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Region = _t, Product = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Region"}, {{"Count", each _, type table [Region=nullable text, Product=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Products", each Text.Combine([Count][Product],",")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Region", "Products"})
in
    #"Removed Other Columns"

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".

[LazarosC]

Thank you very much for your help.