How to separate category hierarchy within one column through empty spaces

[T800]

Dear PBI Community,

i would like to transform a table like the following picture shows:

it looks the same (red version) when i read it in PBI. 
Is there a way to transform it to the green version?

Here the link for the shown Excel file: https://drive.google.com/file/d/1k8GLQOvikjonApxjep5EJNrt_nuWI96H/view?usp=sharing

Thanks in advance and kind regards

T800

[HotChilli · Accepted solution]

Looks like you can add custom columns based on whether the 1st column has "   " (or a tab character, whatever it really is) at the beginning.

Here's an example for the Head Product:

if Text.StartsWith([ALL Products], "  ") then null else [ALL Products])

You can then do a 'Fill Down' on that column.

----

The "subsub" column will be straightforward if you test for double tab at the start (or "       ",  just a guess) like

if Text.StartsWith([ALL Products],"           ") then [ALL Products] else "")

You can do a Trim on that column.

---------------------

The other column will need a more complex formula e.g. does it begin with 3 spaces but not 6 spaces,  something like that

[Icey · Accepted solution]

Hi  @T800 , 

Please check if this post could help you: Solved: Creating a hierarchy from indented column - Microsoft Power BI Community.

---

I have the following table: 

It has a column that contains levels of indentation signifying a hierarchical relationship. I'd like to ask how this could be unwrapped into the following table please?

 Solution:

---

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgQCpVidaCUFJyCAsBScDUEAynE0AgEYxxgEIBxnICBaQywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Block = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Level", each "Block Level " & Number.ToText( List.Count( List.Accumulate( Text.ToList( [Block] ), {" "}, ( s,a ) => if a = List.Last(s) then s & {a} else s ) ) - 1 ) ),
    #"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"Block", Text.Trim, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Trimmed Text", List.Distinct(#"Trimmed Text"[Level]), "Level", "Block"),
    #"Filled Down" = Table.FillDown(#"Pivoted Column",{"Block Level 0", "Block Level 1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Block Level 2] <> null))
in
    #"Filtered Rows"

Best Regards,

Icey

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

[Anonymous · Accepted solution]

you can see the heading blanks checking the box how you can see in the fig.

from you description is not clear how manage value after flattening th product keys

another thing that is not specified is how do you understand that sspA2.1 and sspA2.2 are sp of A2 and not of A1?

....

un bozza in attesa dei tuoi chiarimenti

[Icey · Accepted solution]

Hi  @T800 , 

Please check if this post could help you: Solved: Creating a hierarchy from indented column - Microsoft Power BI Community.

---

I have the following table: 

It has a column that contains levels of indentation signifying a hierarchical relationship. I'd like to ask how this could be unwrapped into the following table please?

 Solution:

---

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcgQCpVidaCUFJyCAsBScDUEAynE0AgEYxxgEIBxnICBaQywA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Block = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Level", each "Block Level " & Number.ToText( List.Count( List.Accumulate( Text.ToList( [Block] ), {" "}, ( s,a ) => if a = List.Last(s) then s & {a} else s ) ) - 1 ) ),
    #"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"Block", Text.Trim, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Trimmed Text", List.Distinct(#"Trimmed Text"[Level]), "Level", "Block"),
    #"Filled Down" = Table.FillDown(#"Pivoted Column",{"Block Level 0", "Block Level 1"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Block Level 2] <> null))
in
    #"Filtered Rows"

Best Regards,

Icey

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

[HotChilli · Accepted solution]

Looks like you can add custom columns based on whether the 1st column has "   " (or a tab character, whatever it really is) at the beginning.

Here's an example for the Head Product:

if Text.StartsWith([ALL Products], "  ") then null else [ALL Products])

You can then do a 'Fill Down' on that column.

----

The "subsub" column will be straightforward if you test for double tab at the start (or "       ",  just a guess) like

if Text.StartsWith([ALL Products],"           ") then [ALL Products] else "")

You can do a Trim on that column.

---------------------

The other column will need a more complex formula e.g. does it begin with 3 spaces but not 6 spaces,  something like that