Formatting a very bad database

[Gionedis]

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:

i dont even know if its possible to do this in power query? if you guys could help i'll be grateful

Thanks

[mlsx4]

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