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:

 

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

 

Thanks

 

Need Help

1 Reply

  • 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