Import data already in matrix style to show in a matrix visual

[DanS446]

Greetings, i have this data that is already organized as a matrix in Excel

It imports like this in power query

Doing some treatment in power query i can somehow show it as desired

let
    Origen = #"Indicadores CIER (2)",
    #"Conservar filas superiores" = Table.FirstN(Origen,11),
    #"Tabla transpuesta" = Table.Transpose(#"Conservar filas superiores"),
    #"Encabezados promovidos" = Table.PromoteHeaders(#"Tabla transpuesta", [PromoteAllScalars=true]),
    #"Conservar filas superiores1" = Table.FirstN(#"Encabezados promovidos",16),
    #"Índice agregado" = Table.AddIndexColumn(#"Conservar filas superiores1", "Índice", 0, 1, Int64.Type),
    #"Columnas reordenadas" = Table.ReorderColumns(#"Índice agregado",{"Índice", "Column1", "Longitud de circuitos [km]", "> 230 kV", "230 - 220 kV", "< 220 kV", "Número de paños", "> 230 kV_1", "230 - 220 kV_2", "< 220 kV_3", "Transformación [MVA]", "Transformación"}),
    #"Valor reemplazado" = Table.ReplaceValue(#"Columnas reordenadas",null," ",Replacer.ReplaceValue,{"Column1", "Longitud de circuitos [km]", "> 230 kV", "230 - 220 kV", "< 220 kV", "Número de paños", "> 230 kV_1", "230 - 220 kV_2", "< 220 kV_3", "Transformación [MVA]", "Transformación"}),
    #"Otras columnas con anulación de dinamización" = Table.UnpivotOtherColumns(#"Valor reemplazado", {"Índice", "Column1"}, "Atributo", "Valor"),
    #"Filas agrupadas" = Table.Group(#"Otras columnas con anulación de dinamización", {"Atributo"}, {{"Recuento", each _, type table [Índice=number, Column1=any, Atributo=text, Valor=number]}}),
    #"Índice agregado1" = Table.AddIndexColumn(#"Filas agrupadas", "Índice", 0, 1, Int64.Type),
    #"Otras columnas quitadas" = Table.SelectColumns(#"Índice agregado1",{"Recuento", "Índice"}),
    #"Se expandió Recuento" = Table.ExpandTableColumn(#"Otras columnas quitadas", "Recuento", {"Índice", "Column1", "Atributo", "Valor"}, {"Recuento.Índice", "Recuento.Column1", "Recuento.Atributo", "Recuento.Valor"})
in
    #"Se expandió Recuento"

 Mainly unpivot to arrange in a Matrix visual and having the rows/columns in the proper order via index numbers.

But im missing the rows categories (Longitud ..., Numero ..., Transforma...), i would need to have those categories for each group of rows (">230 kV" - "230-220 kV" - "<220 kV"), wich is already in the data but as a row (even with the subtotals). 

How could i treat this data to have it proper for a matrix visual?, it even has double header columns so showing it as a regular table also didnt work so well. The idea is to make it look like this in the end, even removing the subtotal rows since they are not necessary.

Thanks in advance.

[Anonymous]

Hi @DanS446 ,

Can you confirm if your missing categories originally appeared as row headings or are they mixed in the data?

Best Regards,
Adamk Kong

[DanS446]

They are mixed in the data, so i need to remove them since they act as sub totals. And use them as a category column for the data like the last image.