Matrix consecutive stepped data

[edwinb]

I have data in an excel file structured like in the table below. My goal is to be able to use the Matrix visual with the +/- icons to expand the code groups A, B, C by the consecutive levels. I've tested breaking the code column into different columns in excel and the query editor. I also tried to setup a heirarchy in Power BI with no luck. Any suggestions on the cleanest way to accomplish this? 

Thanks!

Code	Description	Level
A	Substructure	1
A10	Foundations	2
A1010	Standard Foundations	3
A1010100	Footings & Pile Caps	4
A1010110	Strip Footings	5
A1010120	Spread Footings	5
A1010130	Pile Caps	5
B	Shell	1
B10	Superstructure	2
B1010	Floor Construction	3
B1010100	Suspended Basement Floor Construction	4
B1010200	Upper Floor Framing - Vertical Elements	4
B1010210	Bearing Walls - CIP	5
B1010215	Bearing Walls - Block	5
C	Interiors	1
C10	Interior Construction	2
C1010	Partitions	3
C1010100	Fixed Partitions	4
C1010110	"Partitions - Brick, Solid"	5
C1010115	Partitions - Brick Veneer w/ Stud	5

[speedramps · Accepted solution]

Hi edwin

Please consider this solution and leave kudos:-

Add new columns in your query:-

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Description", type text}, {"Level", Int64.Type}}),

    #"Inserted First Characters" = Table.AddColumn(#"Changed Type", "Product group", each Text.Start([Code], 1), type text),

    #"Inserted Text Range" = Table.AddColumn(#"Inserted First Characters", "Product category", each Text.Middle([Code], 1, 2), type text),

    #"Inserted Text Range1" = Table.AddColumn(#"Inserted Text Range", "Product subcat", each Text.Middle([Code], 3, 2), type text),

    #"Renamed Columns" = Table.RenameColumns(#"Inserted Text Range1",{{"Code", "Product code"}})

in

    #"Renamed Columns" 

Create a hierarchy with:-

Product group                   (character 1)

Product category              (character 2-3)

Product subcategory        (character 4-5)

Product code                    (character 1-8)

Drag the hierarchy to the matrix and use +/- to expand and collapse each level

Click here to download example 

[edwinb]

@speedramps 

Thank you for the help!