Structure data by rows from indented rows Excel-CodePudding

I have the following hierarchical data indented as the following image:

Accounting Entry
Date
Description
Accounting account

I would like to get each indentation level as a column in a new dataset, as in the following image:

I have tried to transpose matrix, power query m, without getting the desired result, there are many records and I need something as automatic as possible.

Maybe with python pandas I can do it?

Thank you very much.

CodePudding user response：

If the data is in one column then for PowerQuery, all you need to know is how to count leading spaces

 = Text.Length([Column1])-Text.Length(Text.TrimStart([Column1]))

From there, make a few if/thens, fill down, then filter

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Entry", each if Text.Length([Column1])-Text.Length(Text.TrimStart([Column1])) = 0 then [Column1] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Date", each if Text.Length([Column1])-Text.Length(Text.TrimStart([Column1])) = 1 then [Column1] else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Description", each if Text.Length([Column1])-Text.Length(Text.TrimStart([Column1])) = 2 then [Column1] else null),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "Account", each if Text.Length([Column1])-Text.Length(Text.TrimStart([Column1])) = 3 then [Column1] else null),
#"Filled Down" = Table.FillDown(#"Added Custom3",{"Entry", "Date", "Description", "Account"}),
#"Added Custom4" = Table.AddColumn(#"Filled Down", "Custom", each Text.Length([Column1])-Text.Length(Text.TrimStart([Column1]))),
#"Filtered Rows" = Table.SelectRows(#"Added Custom4", each ([Custom] = 3)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1", "Custom"})
in  #"Removed Columns"