Forum Discussion

Gionedis's avatar
Gionedis
Helper I
2 years ago

Formatting a very bad database

Hello,   i only have this database that my system exports. the problem is that the supplier name only shows in the result row, like below:     i need something like this to work:   ...
Need Help
mlsx4's avatar
mlsx4
Memorable Member
2 years ago

Hi Gionedis!

 

It may not be the most beautiful solution but I think it works:

 

 

let
    Origen = Excel.Workbook(File.Contents("C:\ex.xlsx"), null, true),
    Hoja1_Sheet = Origen{[Item="Hoja1",Kind="Sheet"]}[Data],
    #"Encabezados promovidos" = Table.PromoteHeaders(Hoja1_Sheet, [PromoteAllScalars=true]),
    #"Columna condicional agregada" = Table.AddColumn(#"Encabezados promovidos", "Fornecedor", each if not Text.StartsWith([#"Histórico/Fornecedor"], "NFS") then [#"Histórico/Fornecedor"] else null),
    #"Rellenar hacia arriba" = Table.FillUp(#"Columna condicional agregada",{"Fornecedor"}),
    #"Filas filtradas" = Table.SelectRows(#"Rellenar hacia arriba", each ([Dt.Lançto] <> "TOTAL POR Fornecedor"))
in
    #"Filas filtradas"

 

 

 

What I have done is:

  • Create a conditional column: if not start with NFS return column historico/fornecedor else null
  • Now, use fill up function (it is in Transform tab>Fill>Up)
  • And finally, filter rows <> total por fornecedor