How to create hierarchy bases on column values

[DCS2011]

Have a table simialir to the following, and I would like to create a hierarchy based on the values. Each level is spaced (indent) different and it goes down like 10+ levels. Would like to be able to create slicer that can show everything assigned to Level 1, then see what within Level 1 is assigned to Level 1.1. Do need to go down to all levels, at least 4. 

Code	Name
ABC.01	T
ABC.01.01	U
ABC.01.01.01	V
ABC.02	W
ABC.02.01	X
ABC.02.01.01	Y

[v-jianboli-msft · Accepted solution]

Hi @DCS2011 ,

Please try:

First add two columns:

Then pivot your data:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRy1jMwVNJRClGK1YFxISKhqCIQwTCEoBGQG47EhSiIQBWBCEYqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Level", each Text.Length(
Text.Select([Code],"."))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Category", each Text.Range([Code],Text.PositionOf([Code],".")+1,
 try Text.PositionOf([Code],".",Occurrence.All){1} -Text.PositionOf([Code],".")-1
 otherwise Text.Length([Code]) -Text.PositionOf([Code],".")-1
 )),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Code"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Level", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Level", type text}}, "en-US")[Level]), "Level", "Name")
in
    #"Pivoted Column"

Then create a hierarchy:

Final output:

Best Regards,

Jianbo Li

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