Using Powerquery to extract hierarchy from Excel indent

Question

I am using a business application that exports .xls data files for analysis. I want to import these into Excel using PowerQuery.

There is a hierarchy to the data, but it is signified only by the indent of the first column. There are no leading zeros or other characters.

Powerquery M doesn't seem to have a function to return the indent level.

Other approaches I've found rely on counting leading zeros, but that won't work for me.

Along the way, I have written a simple Excel custom function using the Cell.IndentLevel attribute works well enough, but I'd like to get PowerQuery to do this so there is just one import code.

Q: Can Powerquery access the Excel cell.indentlevel value? Or can it execute a custom Excel function? How else might I approach this?

user_11056849 · Answer

EDIT - Ron Rosenfield mentioned quite rightly that this indent issue may be Excel Based, Doesn't look like Power query has an indent handler.

This is likely because Excel Indent Level is not an ISO method of Data heirarchy.

So you can just apply a helper column that will brute force give the level of indent then power query can take care of the rest.

This should do it, modify as you need and it will detect and apply a number

Sub Macro1()

    Dim MyCell As Variant
    Range("A:A").Insert
    For Each MyCell In Range("A1:A10") 'Change this to be Dynamic
        MyCell.Value = MyCell.Offset(, 1).IndentLevel
    Next MyCell
    
End Sub

Oooh also staging stoof from external... "Transform" Part of ETL (Extract, Transform, Load) Well, Most of the time indents are handled by spaces.

Do you have any other options than XLS, as the indent may be handled differently in CSV.

In the un-ironically named, Transform Tab of the Power Query ribbon you can use Split Columns by the Delimiter Space. (One of the more common delimiters for indent besides Tab.)

This will then allow you to group the data, a little messy if there are other spaces but you can make steps to handle that

Alternatively there are other options in the Transform Tab you can use failing that, as there is no example of the system you are exporting from it's difficult to say if this is an Excel Indent or if Indent actually applies.

Result

Using Powerquery to extract hierarchy from Excel indent

1 Answers