Excel grouping row function

[elmer]

Goodmorning to everyone,

Here's the issue: I'm trying to upload an excel as dataset of my report in power Bi.

The problem is that the file excel is formatted with the grouping function, that represent a gerarchy as follow, but power Bi doesnt read the tabulations, so it returns the data in the "Area" column without any hint about the level of hierarchy of every single item (the numbers inside the parenthesis, represent the number of users of each Area).

Here's the originary hierarchy:

Here's the final result in Bi:

There's a way to rebuild the originary hierarchy?

Thanks in advance,

Elmer

[elmer]

Sure! Attached in this response the requested sample file.

As you see the problem is that excel regroup the rows and that causes some trouble in uploading the data in power Bi... 

Excel sample 

[KNP]

I need examples of the actual values in your actual file. 

You said

"but in the originale excel file, I have the names of the different unit of my organization, that doesn't have a number that categorize them in a specified level of the hierarchy."

I need a sample with the unit names so I can figure out how to adapt my solution.

If you could provide an actual file with the sensitive info masked?

[elmer]

Hi KNP, sorry for the late reply...

Here's a more accurate example of what I have as a dataset:

sample 2 

[elmer]

The issue is that it seems that power bi cannot read the indent of the original excel file

[KNP]

I see what you mean about the "indent" of the grouping.

I can't find anywhere that this grouping is shown in the meta data that is visible to Power Query, so unfortunately, I think you're going to have to modify the source file somehow to add an identifier for the grouping levels.

[elmer]

I think I found a solution with a macro, so I can modify my original file and then give it to power bi. The next problem is how can i flatten the hierarchy once I upload my dataset in Power Bi. I tried everything (form sostitution, to path, pathitem, etc...) but I cant find a solution.

This is my new excel: sample 3 

[KNP]

Hi Elmer,

Without having a sample file to work with, I'm not sure this will work, but paste the below into the advanced editor of a new blank query to see what it is doing.

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText("i45WSixKTVQwVNAwMjLSVIrVgQoYKWiYmiLxjRU0DA2A/FgA", BinaryEncoding.Base64),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Column1 = _t]
  ),
  #"Split Column by Delimiter" = Table.SplitColumn(
    Source,
    "Column1",
    Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
    {"Column1.1", "Column1.2", "Column1.3"}
  ),
  #"Changed Type" = Table.TransformColumnTypes(
    #"Split Column by Delimiter",
    {{"Column1.3", type number}}
  ),
  #"Multiplied Column" = Table.TransformColumns(
    #"Changed Type",
    {{"Column1.3", each _ * - 1, type number}}
  )
in
  #"Multiplied Column"

Basically, splitting on every space.

Converting number of users to a number (because it is in parenthesis it converts to negative)

Multiply by -1 to get positive.

I hope this helps.

[elmer]

Hi KNP!

Thanks for the response.

Unfortunately, your solution can't work in my case: the rows that I have attached were an example, but in the originale excel file, I have the names of the different unit of my organization, that doesn't have a number that categorize them in a specified level of the hierarchy.

The other problem is that the excel regroup the rows, so when I upload the dataset I don't have spaces, but tabulation and it seems that Power Bi isnt able to read the tabs.

[KNP]

It can most definitely read tabs.

Can you please provide a sample table (not screenshot) of actual data, or something that more closely resembles it so I can try and provide something that will work?