Forum Discussion

NickzNickz's avatar
NickzNickz
Icon for Helper IV rankHelper IV
3 years ago

Get data from excel based on column

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 ...
Need Help
Show and Tell
Tips and Tricks
mlsx4's avatar
mlsx4
Icon for Memorable Member rankMemorable Member
3 years ago

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