Model data source - Power BI

I'm trying to model a data source in power bi, but I'm not getting it.

Could you help me with alternatives so I can create a new column? The data source is in excel and brings the data with subtotal by types (XPTO, XPT, etc). I want to put these types as corresponding values in the new column for your items. I tried via power query and dax, but I could not.

Original Source:

Modifications Needed

Source File

Solution

This assumes that you can identify the Subtotal Rows by checking the position of the first space in the first column:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added RowType" = Table.AddColumn(Source, "RowType", each Text.PositionOf([#"Centro financ./item orçamento"]," "), Int64.Type),
    #"Added Type" = Table.AddColumn(#"Added RowType", "Type", each if [RowType] = 4 then Text.BetweenDelimiters([#"Centro financ./item orçamento"], "  ", " ") else null, type text),
    #"Filled Down" = Table.FillDown(#"Added Type",{"Type"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([RowType] = 8)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"RowType"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",List.Combine({{"Type"}, Table.ColumnNames(Source)}))
in
    #"Reordered Columns"