Flatten One-column Hierarchy into a Table

[Haleem]

What I have (Table 1)

I have cost center hierarchy from SAP extracted excel file. This hierarcy is all in one Column as shown below with various levels (Table 1). The lowest level of the hierarchy is a 5-digit cost center  (unique numbers).

What I would like to have (Table 2)

I would like to flatten this one-column hierarchy into a table as shown on the right side. I need to start in reverse order, so the cost center will be the last level of the hierarchy and then going backward  to capture each level as shown in table two. Please remember that the Table 2 should be dynamically updating each a new cost center is added to the table 1. There is no standard for on which level the cost center should be placed, so business division have many layer before reaching to a cost center and other are just on 3rd or 4th level as shown in the table 1. 

[ziying35]

Hi, @Haleem 

Could you upload a file with sensitive information removed to a cloud disk and then share the link?

[Haleem]

https://www.dropbox.com/scl/fi/bx3pg7bgxzqo95x7tod46/cc-data.xlsx?dl=0&rlkey=5mbu6myljp3j2ckqg6wdv6n...

here is the sample data.

[Fowmy]

@Haleem 

Can you share some sample data ?

Thanks

[Greg_Deckler]

@Haleem - How many of these do you have? Might be better just to type it. Failing that, you would need to do something to count the spaces at the beginning of text for each row and use that to compute the position in the hierarchy and then go from there. Ugly.

[Haleem]

@Greg_Deckler I have more than 1000 rows in the hierarchy which is constantly updating everytime a new cost center is placed into it. It's impossible to type it manually. Yes It's ugly that's why I am here   🙂

[Greg_Deckler]

@Haleem - Can you post sample source data as text?

[Haleem]

https://www.dropbox.com/scl/fi/bx3pg7bgxzqo95x7tod46/cc-data.xlsx?dl=0&rlkey=5mbu6myljp3j2ckqg6wdv6n...

[Fowmy]

@Haleem 

Is there any consistent pattern in the codes like the order of digits, number series that distinguish between different levels. The file you shared, the indentations get cleaned up while loading to Power Query.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Haleem]

@Fowmy No there is no such consistent or logical pattern used to define the sequence of the levels.

[Anonymous]

antother way is to navigate and find relation between xml files/tables in the unziped structure of excel file.

my idea is that the indentation level corresponds to the number in the column
Table.c.Attribute: s
from this point on you can work on a query

[Anonymous]

let
        Source = File.Contents("C:\Users\abcdefg\OneDrive - TIM\MyD2020\BI\cccdata.xlsx"),
        Files = UnZip(Source),
    #"Added Custom" = Table.AddColumn(Files, "xml", each Xml.Tables([Content])),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"xml"}),
    #"Expanded xml" = Table.ExpandTableColumn(#"Removed Errors", "xml", {"Name", "Table"}, {"xml.Name", "xml.Table"})
in
    #"Expanded xml"

unZip function from:

http://sql10.blogspot.com/2016/06/reading-zip-files-in-powerquery-m.html

expand and merge these tables:

let
        Source = File.Contents("C:\Users\abcdefg\OneDrive - TIM\MyD2020\BI\cccdata.xlsx"),
        Files = UnZip(Source),
    #"Filtered Hidden Files1" = Table.SelectRows(Files, each [Attributes]?[Hidden]? <> true),
    #"Inserted Parsed XML" = Table.AddColumn(#"Filtered Hidden Files1", "XML", each Xml.Tables([Content])),
    #"Expanded XML" = Table.ExpandTableColumn(#"Inserted Parsed XML", "XML", {"Name", "Table"}, {"XML.Name", "XML.Table"}),
    #"XML Table1" = #"Expanded XML"{16}[XML.Table],
    #"Expanded numFmt" = Table.ExpandTableColumn(#"XML Table1", "numFmt", {"Attribute:numFmtId", "Attribute:formatCode"}, {"numFmt.Attribute:numFmtId", "numFmt.Attribute:formatCode"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded numFmt", "Index", 0, 1)
in
    #"Added Index"

let
        Source = File.Contents("C:\Users\abcdefg\OneDrive - TIM\MyD2020\BI\cccdata.xlsx"),
        Files = UnZip(Source),
    #"Filtered Hidden Files1" = Table.SelectRows(Files, each [Attributes]?[Hidden]? <> true),
    #"Inserted Parsed XML" = Table.AddColumn(#"Filtered Hidden Files1", "XML", each Xml.Tables([Content])),
    #"Expanded XML" = Table.ExpandTableColumn(#"Inserted Parsed XML", "XML", {"Name", "Table"}, {"XML.Name", "XML.Table"}),
    #"XML Table1" = #"Expanded XML"{33}[XML.Table],
    #"Expanded Table" = Table.ExpandTableColumn(#"XML Table1", "Table", {"c", "Attribute:r", "Attribute:spans", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"}, {"Table.c", "Table.Attribute:r", "Table.Attribute:spans", "Table.http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac"}),
    #"Expanded Table.c" = Table.ExpandTableColumn(#"Expanded Table", "Table.c", {"v", "Attribute:r", "Attribute:s", "Attribute:t"}, {"Table.c.v", "Table.c.Attribute:r", "Table.c.Attribute:s", "Table.c.Attribute:t"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded Table.c",{"Table.c.Attribute:s", "Table.c.Attribute:r", "Table.c.v"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Table.c.v", Int64.Type}})
in
    #"Changed Type"

let
        Source = File.Contents("C:\Users\abcdefg\OneDrive - TIM\MyD2020\BI\cccdata.xlsx"),
        Files = UnZip(Source),
    #"Added Custom" = Table.AddColumn(Files, "xml", each Xml.Tables([Content])),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"xml"}),
    xml1 = #"Removed Errors"{4}[xml],
    #"Expanded si" = Table.ExpandTableColumn(xml1, "si", {"t"}, {"si.t"}),
    #"Expanded si.t" = Table.ExpandTableColumn(#"Expanded si", "si.t", {"Element:Text"}, {"si.t.Element:Text"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded si.t",{"si.t.Element:Text"}),
    #"Added Index" = Table.AddIndexColumn(#"Removed Other Columns", "Index", 0, 1)
in
    #"Added Index"

merge, pivot and filldown!

let
    Source = Table.NestedJoin(rowData, {"Table.c.v"}, sharedStrings, {"Index"}, "sharedStrings", JoinKind.LeftOuter),
    #"Expanded sharedStrings" = Table.ExpandTableColumn(Source, "sharedStrings", {"si.t.Element:Text", "Index"}, {"sharedStrings.si.t.Element:Text", "sharedStrings.Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded sharedStrings", each Text.Contains([#"Table.c.Attribute:r"], "A")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Table.c.Attribute:s", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Table.c.Attribute:s"}, numFormats, {"Index"}, "numFormats", JoinKind.LeftOuter),
    #"Expanded numFormats" = Table.ExpandTableColumn(#"Merged Queries", "numFormats", {"numFmt.Attribute:numFmtId", "numFmt.Attribute:formatCode", "Attribute:count", "Index"}, {"numFormats.numFmt.Attribute:numFmtId", "numFormats.numFmt.Attribute:formatCode", "numFormats.Attribute:count", "numFormats.Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded numFormats", "AtoZ", each Text.Start([#"Table.c.Attribute:r"],1)&Text.PadStart(Text.Remove([#"Table.c.Attribute:r"],"A"),4,"0")),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"AtoZ", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Table.c.Attribute:r", "Table.c.v", "sharedStrings.Index", "numFormats.numFmt.Attribute:numFmtId", "numFormats.Attribute:count", "numFormats.Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Table.c.Attribute:s", type text}}, "it-IT"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Table.c.Attribute:s", type text}}, "it-IT")[#"Table.c.Attribute:s"]), "Table.c.Attribute:s", "sharedStrings.si.t.Element:Text"),
    #"Sorted Rows1" = Table.Sort(#"Pivoted Column",{{"AtoZ", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows1",{"1", "2", "3", "5", "7","9","11","13"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down",null,"",Replacer.ReplaceValue,{"numFormats.numFmt.Attribute:formatCode", "AtoZ", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13"})
in
    #"Replaced Value"

[Anonymous]

modify the last script in this way:

let
    Source = Table.NestedJoin(rowData, {"Table.c.v"}, sharedStrings, {"Index"}, "sharedStrings", JoinKind.LeftOuter),
    #"Expanded sharedStrings" = Table.ExpandTableColumn(Source, "sharedStrings", {"si.t.Element:Text", "Index"}, {"sharedStrings.si.t.Element:Text", "sharedStrings.Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded sharedStrings", each Text.Contains([#"Table.c.Attribute:r"], "A")),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Table.c.Attribute:s", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Table.c.Attribute:s"}, numFormats, {"Index"}, "numFormats", JoinKind.LeftOuter),
    #"Expanded numFormats" = Table.ExpandTableColumn(#"Merged Queries", "numFormats", {"numFmt.Attribute:numFmtId", "numFmt.Attribute:formatCode", "Attribute:count", "Index"}, {"numFormats.numFmt.Attribute:numFmtId", "numFormats.numFmt.Attribute:formatCode", "numFormats.Attribute:count", "numFormats.Index"}),
    #"Added Custom" = Table.AddColumn(#"Expanded numFormats", "AtoZ", each Text.Start([#"Table.c.Attribute:r"],1)&Text.PadStart(Text.Remove([#"Table.c.Attribute:r"],"A"),4,"0")),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"AtoZ", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Table.c.Attribute:r", "Table.c.v", "sharedStrings.Index", "numFormats.numFmt.Attribute:numFmtId", "numFormats.Attribute:count", "numFormats.Index"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Table.c.Attribute:s", type text}}, "it-IT"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Table.c.Attribute:s", type text}}, "it-IT")[#"Table.c.Attribute:s"]), "Table.c.Attribute:s", "sharedStrings.si.t.Element:Text"),
    #"Sorted Rows1" = Table.Sort(#"Pivoted Column",{{"AtoZ", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows1",{"1", "2", "3", "5", "7","9","11","13"}),
    #"Replaced Value" = Table.ReplaceValue(#"Filled Down",null,"",Replacer.ReplaceValue,{"numFormats.numFmt.Attribute:formatCode", "AtoZ", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13"}),
    #"Filtered Rows1" = Table.SelectRows(#"Replaced Value", each not Text.Contains([#"numFormats.numFmt.Attribute:formatCode"], "-"))
in
    #"Filtered Rows1"

PS

I wrote a new message as I was unable to correct the old one. ☹️

[ziying35]

Hi, @Anonymous 

Your idea is good, but after decompression into XML, isn't it on different computers have different index position, because the position is different, so I'm on my computer can not run normally, I'd like to have these as index as dynamic judgment is better. This is my personal ideas, not necessarily correct

[Anonymous]

Hi @ziying35 ,

surely the solution I found is absolutely not future proof.
For me it was an interesting experiment on things I don't know and have never done.
It's a very laborious procedure and that's why I didn't have much time to think about making it "dynamic".
I tried to do everything via GUI and apart from very few lines of code it was.
For the differences you have noticed, keep in mind that I have "cleaned" the file by changing some aspects of the formatting of the excel sheet that contains the table and eliminating a "very hidden" sheet and other things that I don't remember.
Therefore if you have used the original workbook you will surely find a different structure and content.
I just wanted to give you an idea. If deemed of interest and valid, we can work to make it "usable"

the link where those interested in verifying the idea find the files used:

https://giga.allegati.tim.it/r?d=alice.it&wr=rocco.lupoi@telecomitalia.it&ws=sprmnt21&e=alice.it&c=j...

[Fowmy]

@Haleem 

I checked how the hierarchy is built in your file. Actually there is no consistency. Whoever did this, has applied custom formatting with space as prefix like "    "@.  

Before we do any transformation, get his levels organized in proper order, then you can get the file to be transformed as you expected.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

itis not clear to me, in the picturs you provided, why you have so many levels with repeated contents and not stop to 5 levels.

In any case, I believe that before managing data with PBi it is necessary to use an excel macro that reads the formats of each cell in the column to see the number of spaces used for indentation.
that is it true IF you want the same level where there is the same indentation

[Haleem]

Is there any way to send you the file privately. Not here in public