Get data from excel based on column

[NickzNickz]

Hi...

I have received the information for Q4 below in Excel. 

Can anyone suggest/recommend me a better way how can I extract the data in Power BI and how to do it ?...

Data is cumulative and updated every quarter. I also believed that I need to have a date column.

Should I go for like this or 

should I for this format:

Regards,
NickzNickz

[v-jianboli-msft]

Hi @NickzNickz ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mlsx4]

Hi @NickzNickz 

Try to add this code to the advance editor (keeping the source and navigation)

    #"Encabezados promovidos" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos",{{"No", Int64.Type}, {"State", type text}, {"Major", type any}, {"Column4", type any}, {"Minor", type any}, {"Column6", type any}}),
    #"Columna de anulación de dinamización" = Table.UnpivotOtherColumns(#"Tipo cambiado", {"No", "State"}, "Atributo", "Valor"),
    #"Filas filtradas" = Table.SelectRows(#"Columna de anulación de dinamización", each ([Atributo] = "Column4" or [Atributo] = "Column6")),
    #"Valor reemplazado" = Table.ReplaceValue(#"Filas filtradas","Column4","Major",Replacer.ReplaceText,{"Atributo"}),
    #"Valor reemplazado1" = Table.ReplaceValue(#"Valor reemplazado","Column6","Minor",Replacer.ReplaceText,{"Atributo"}),
    #"Filas superiores quitadas" = Table.Skip(#"Valor reemplazado1",2),
    #"Filas ordenadas" = Table.Sort(#"Filas superiores quitadas",{{"Atributo", Order.Ascending}, {"Valor", Order.Ascending}})
in
    #"Filas ordenadas"

It should work. 

What I have done is to promote headers, unpivot, filter data (to keep only the values I need), then replace by major or minor, delete the top rows and that's it