Help splitting a column based on the number of leading spaces

[gboldt]

I am attempting to clean some data that has all the parent and child records in column A and the hierarchy is defined by the number of spaces. Example data:

I want to transpose each value in the "zone" column to the appropriate column based on the number of leading spaces (note that the column names and actual data will not have anything in common like they do in the example):

1. If 1 leading space, leave where it is (zone)
2. If 4 leading spaces, transpose 1 column over (unit)
3. If 7 leading spaces, transpose 2 columns over (category)
4. If 10 leading spaces, transpose 3 columns over (status)

I also want to copy parent values down so that if it has a child record it is populated. Here is what the result would look like based on the example data above: 

any help would be greatly appreciated, thanks in advance.

[v-yalanwu-msft · Accepted solution]

Hi, @gboldt ;

You could complete it in power query.

1.add conditional formula.

= Table.AddColumn(Source, "zone1", each if not Text.StartsWith([zone], " ") then [zone] else null)

= Table.AddColumn(#"Added Conditional Column", "unit", each if Text.StartsWith([zone]," ") and not Text.StartsWith([zone],"  ") then [zone] else null)

= Table.AddColumn(#"Added Custom", "category", each if Text.StartsWith([zone],"  ") and not Text.StartsWith([zone],"   ") then [zone] else null)

= Table.AddColumn(#"Added Custom1", "Status", each if Text.StartsWith([zone],"   ") and not Text.StartsWith([zone],"     ") then [zone] else null)

2.fill down every column.

= Table.FillDown(#"Added Custom2",{"zone1"})

= Table.Group(#"Filled Down", {"zone1"}, {{"a", each Table.FillDown(_,{"unit"}), type table [zone=nullable text, zone1=text, unit=nullable text, category=nullable text, Status=nullable text]}})

= Table.Group(#"Expanded a", {"zone1", "unit"}, {{"category", each Table.FillDown(_,{"category"}), type table [zone1=text, unit=nullable text, category=nullable text, Status=nullable text]}})

The final show as follow:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqsrPS1VIVIrViVZSKM3LLDGEMBWSE0tS0/OLKhVgAgoKxSWJJaXFCKVG2JWCTExCqDJGVwXUFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [zone = _t]),
    #"Added Conditional Column" = Table.AddColumn(Source, "zone1", each if not Text.StartsWith([zone], " ") then [zone] else null),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "unit", each if Text.StartsWith([zone]," ") and not Text.StartsWith([zone],"  ") then [zone] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "category", each if Text.StartsWith([zone],"  ") and not Text.StartsWith([zone],"   ") then [zone] else null),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Status", each if Text.StartsWith([zone],"   ") and not Text.StartsWith([zone],"     ") then [zone] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom2",{"zone1"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"zone1"}, {{"a", each Table.FillDown(_,{"unit"}), type table [zone=nullable text, zone1=text, unit=nullable text, category=nullable text, Status=nullable text]}}),
    #"Expanded a" = Table.ExpandTableColumn(#"Grouped Rows", "a", {"unit", "category", "Status"}, {"unit", "category", "Status"}),
    #"Grouped Rows1" = Table.Group(#"Expanded a", {"zone1", "unit"}, {{"category", each Table.FillDown(_,{"category"}), type table [zone1=text, unit=nullable text, category=nullable text, Status=nullable text]}}),
    #"Expanded category" = Table.ExpandTableColumn(#"Grouped Rows1", "category", {"category", "Status"}, {"category", "Status"})
in
    #"Expanded category"

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.